Question 114965 in PostgreSQL Question posted in
The official documentation can be found here.

postgreSQL – Need to extract lat/lon from geography data tyle column

It seems this particular topic has quite a bit of traction here, but I have only been able to get one example to work, but I’m not sure if it will work for my use case. Please note I am new to PostgreSQL so go easy. I’ve learned a ton from this and other sites so that is how I have been getting things done thus far.

The ultimate goal is to extract lat/lon information so it can be plotted on a Google Map. There is a column called "outline" that is a "geography" data type that contains the information needed to extract a lat/lon. Every example I have found here doesn’t seem to work and from what I have read, it needs to be cast to geometry, which I have tried. Here is a really simple example I have tried just to test if it works:

SELECT i.site_id, ST_X(outline::geometry), ST_Y(outline::geometry) FROM images i

The result is "Argument to ST_X() must be a point.

For reference, here is an example of the data that is in this particular outline column:

0106000020E610000001000000010300000001000000140000009302054913715EC03C9AA11C6B5B4240DE1BD34012715EC0EB9929C36A5B4240D7D2B1E2F8705EC03D71B96B545B4240109BD746F8705EC08619459C535B4240E2F80EF4F7705EC08548B094525B4240450FCFFCF7705EC0AF8450CE505B42402A34C030FE705EC02AF11413345B4240F068643F02715EC0CFC974D7265B4240C45BBBAF02715EC0215FA0EC255B4240A6F9694E03715EC029AB0B5A255B42401964A26004715EC0D408F84B255B42407730500805715EC0128533D3255B4240366AC6A11E715EC092CD249F455B42400F1088451F715EC0A492CD11475B4240B68C8E591F715EC083BAD747485B42401C8A48271F715EC001D97C5B495B4240707098C314715EC095D2088C695B424033EAEF0514715EC0AA98ABC26A5B42402461084913715EC0360EFC1A6B5B42409302054913715EC03C9AA11C6B5B4240

The only example I have found that does seem to work is:

SELECT 
  i.customer_id, 
  i.captured_at,  
  i.name, 
  i.site_id, 
  i.outline, 
  ST_AsText(ST_Centroid(outline))
FROM images i

This does not error, and the result gives me this format:

POINT(-121.080244930964 36.2187349133648)

I am hoping for a bit of a push towards what ever the best solution may be to produce a lat/lon. The only problem with the output above is I would need to take that and make a new column which will trim POINT( and also the ending ). I would also need to reverse the values, meaning, the new column would need to result in 36.2187349133648, -121.080244930964. I also have no idea if the fact it’s a text field would hurt me in the end. For what it’s worth, I use Google Data Studio for reporting and would use the Google Map control and have the lat/lon column feed the points. I have read it requires the coordinates to be fed in the above example.

Sorry for such a long note, and I would appreciate any advice you may have. I am using PostgreSQL v10.

Tags:

2

Answers


  1. 0

    Your outline is a multipolygon geography. ST_Centroid returns a point geography, which you’ll need to cast to geometry and feed into st_x/st_y, so if you just need those values for lon and lat, you can try

    SELECT
  i.id as id,
  ST_X(i.centroid) as lon,
  ST_Y(i.centroid) as lat
FROM (
  SELECT
    id,
    ST_Centroid(outline)::geometry AS centroid
  FROM images
) i;

    Just modify that to suit your needs.

    NOTE: There are a few different ways to write this exact query (Check out WITH clauses; they’re super useful in specific scenarios), but this is just the way that came to mind first for me.

    EDIT: Definitely take a look at the other solution here too. Lateral joins on a function can be a bit less intuitive at first glance than subqueries but are clearly more optimized if you’re running into performance issues.

    Login or Signup to reply.
  2. 0

    Although the accepted answer works and has its merits, it comes with some performance issues. Extracting the centroid in a subquery and then extracting the x and y values in an outer query means that you’re reading the same data set twice – in a table full scan btw. Try to keep things simple and avoid subqueries like this whenever possible, as it might slow things down significantly. Here is an example of how to do it using LATERAL JOIN instead:

    SELECT 
  gid as id,
  ST_X(centroid) as lon,
  ST_Y(centroid) as lat
FROM images
CROSS JOIN LATERAL ST_Centroid(outline::geometry) AS centroid

    Demo: db<>fiddle

    Table containing 1000 polygons

    Using a LATERAL JOIN

    EXPLAIN (ANALYSE,BUFFERS)
SELECT id,
  ST_X(centroid) as lon,
  ST_Y(centroid) as lat
FROM images
CROSS JOIN LATERAL ST_Centroid(outline::geometry) AS centroid;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1.25..289.84 rows=6731 width=20) (actual time=0.113..6.517 rows=1000 loops=1)
   Buffers: shared hit=53
   ->  Seq Scan on images  (cost=0.00..120.31 rows=6731 width=36) (actual time=0.015..0.236 rows=1000 loops=1)
         Buffers: shared hit=53
   ->  Function Scan on st_centroid centroid  (cost=1.25..1.26 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1000)
 Planning Time: 0.161 ms
 Execution Time: 6.658 ms
(7 rows)

    Using a subquery as proposed in the other answer:

    EXPLAIN (ANALYSE,BUFFERS)
SELECT
  i.id as id,
  ST_X(i.centroid) as lon,
  ST_Y(i.centroid) as lat
FROM (
  SELECT
    id,
    ST_Centroid(outline)::geometry AS centroid
  FROM images
) i;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on images  (cost=0.00..2573.00 rows=1000 width=20) (actual time=0.750..127.119 rows=1000 loops=1)
   Buffers: shared hit=53
 Planning Time: 0.136 ms
 Execution Time: 127.210 ms
(4 rows)

    Note: Keep in mind that PostgreSQL 10 is reaching EOL in a few months. Consider upgrading your system asap.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search