I have this raw text value extracted from Postgres geometry(point) type 0101000020E6100000FAFFEFE0FB444B40672BE61C924E3840
I was wondering if there is any way to get the lat,long values from it without having to insert it into db again and using the ST_X, ST_Y functions!
-
Well-Known Binary is certainly decodable by many GIS tools, though first you'd need to convert hex string to binary.– VinceCommented Jun 3, 2019 at 11:25
-
2SELECT ST_AsText('0101000020E6100000FAFFEFE0FB444B40672BE61C924E3840'); as what you have is already a binary geometry format.– John PowellCommented Jun 3, 2019 at 11:43
Add a comment
|
1 Answer
I cast the data as geometry and extract coordinates after :
SELECT ST_X(t1.geom) AS long, ST_Y(t1.geom) AS lat
FROM (SELECT '0101000020E6100000FAFFEFE0FB444B40672BE61C924E3840'::geometry AS geom) t1
;
answered Jun 3, 2019 at 11:25
-
Try running this:
SELECT ST_X(geom) AS long, ST_Y(geom) AS lat FROM (SELECT ST_GeomFromGeoHash('0101000020E6100000C924E3840') AS geom) t1; and you will see what is wrong with your answer. Commented Jun 3, 2019 at 11:39 -
2This would actually seem to be a bug in GeomFromGeoHash, as the geometry you are feeding to it in no way resembles a geohash and should return an error. Even
SELECT ST_X(geom) AS long, ST_Y(geom) AS lat FROM (SELECT ST_GeomFromGeoHash('01010000IamNotAGeohash') AS geom) t1; returns -179.999821438159 -84.1991949186074 Commented Jun 3, 2019 at 11:46 -
@JohnPowell : I made the modification, hope that is the solution. Commented Jun 3, 2019 at 12:21
-
Yes, that works, although you don't even need the cast, as the string already represents the geometry, though there is nothing wrong with explicit casting. Commented Jun 3, 2019 at 12:33