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

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.

CodePudding user response：

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.