postgresql st_asgeojson returns not readable value

Question

I'm trying to make this data to geometry, but for some reason postgresql returns not readable values. I need latlng.... I can't use that value to draw marker at all.

what am i missing here?

please help.

postgresql v. 11

insert into area(latlng) values('0101000000A0ABA80D249D024100EC624219AA0B41'::geometry);

select st_asgeojson(latlng) from area;

returns

"{""type"":""Point"",""coordinates"":[152484.50666937,226627.157415241]}"

----------------------------------------- added

more queries are added. they always return same value. I'm started thinking the value that I provided from data team, might be wrong...('0101000000A0ABA80D249D024100EC624219AA0B41')

select ST_AsText('0101000000A0ABA80D249D024100EC624219AA0B41'); "POINT(152484.50666936953 226627.157415241)"

select st_setsrid(st_asgeojson('0101000000A0ABA80D249D024100EC624219AA0B41')::geometry, 4326);

select st_transform(st_setsrid(st_asgeojson('0101000000A0ABA80D249D024100EC624219AA0B41'::geometry), 4326), 3857);

A large part of GIS is the projection that the data is encoded in. Can you do `select st_srid(latlng) from area;` and add it. You may need to use `st_transform` to tranform it into a different projection (e.g. WGS84, which is identified as srid 4326, is probably lat/lng). Probably searching up a bunch of these terms might help. ;-) — GregHNZ, Aug 24 '23 at 03:43
@GregHNZ Hey!! Thank you so much for the comment! I've tested with your suggestion and then now it returns `ERROR: transform: Invalid coordinate(2049)` ... should I contact with data team...? — Chaehwa Ryu, Aug 24 '23 at 05:28
@ChaehwaRyu perhaps you've mistakenly created the coordinates as `lat long`? It should be `long lat` (x,y) — Jim Jones, Aug 24 '23 at 06:04
@JimJones Thanks for the opinion! but i've got that data from the data team. I didn't make it. I have zero idea how they converted. i'm just trying to convert it back to latlng(lnglat). I guess this one is just poor as other datasets they provided. I'll check it out!! :) have a great day! — Chaehwa Ryu, Aug 24 '23 at 06:36
these coordinates are clearly not in 4326 (+-180 / +-90). You need to know the SRID so you can 1) set it and 2) convert via st_stransform to 4326. Also you don't set the SRID (nor do anything) on the geojson but on the original geometry — JGH, Aug 24 '23 at 10:42
@ChaehwaRyu these coordinates do not seem to be WGS84 .. check with your team the SRS. Cheers and happy coding! — Jim Jones, Aug 24 '23 at 11:26
Your'e getting the error in your last query because you're giving st_setsrid a geojson string rather than a geometry. Leave out the st_asgeojson. — mlinth, Aug 29 '23 at 12:22
@mlinth (pedantic note) it is not entirely accurate: https://dbfiddle.uk/A7DQSyQt :) — Jim Jones, Aug 29 '23 at 13:14

score 0 · Answer 1 · answered Aug 28 '23 at 07:55

Your input value is geography and not geometry. See below

select st_astext('0101000000A0ABA80D249D024100EC624219AA0B41'::geography);
-- POINT(-155.4933306304738 -7.157415241003036)

select postgis_version();
-- 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

In short geography is more accurate but resource intensive to query. geometry is the 99% good enough version of geography

Info: https://postgis.net/documentation/faq/geometry-or-geography/

postgresql st_asgeojson returns not readable value

1 Answers1