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
– GregHNZ, Commented Aug 24, 2023 at 3: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
– Chaehwa Ryu, Commented Aug 24, 2023 at 5:28
@ChaehwaRyu perhaps you've mistakenly created the coordinates as lat long? It should be long lat (x,y) — Jim Jones
– Jim Jones, Commented Aug 24, 2023 at 6: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
– Chaehwa Ryu, Commented Aug 24, 2023 at 6:36
@mlinth (pedantic note) it is not entirely accurate: dbfiddle.uk/A7DQSyQt :) — Jim Jones
– Jim Jones, Commented Aug 29, 2023 at 13:14

Kevin Potgieter · Accepted Answer · 2023-08-28 07:55:08Z

2

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/

answered Aug 28, 2023 at 7:55

Kevin Potgieter

8123 gold badges13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chaehwa Ryu Over a year ago

Sorry for late, super thanks. it works :)

Collectives™ on Stack Overflow

postgresql st_asgeojson returns not readable value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related