Geographic Information Systems Asked by Thirumal on December 31, 2020
How to select from Point column data type(native) in PostgreSQL?
select a.* from indsolv.address As a where a.location = '93.2321, 21.0321'
Getting error for the above query!
ERROR: operator does not exist: point = unknown
LINE 1: ...ct a.* from indsolv.address As a where a.location = '93.2321...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883
Character: 55
You seem to be using the native PostgreSQL POINT
type rather than the PostGIS GEOMETRY(POINT)
; this answer is for the PostGIS extension! See the answer in the cross post on DBA.SE for the PostgreSQL native type.
You will need to to construct an actual geometry from your input coordinates to compare against values in a GEOMETRY
column, e.g.
ST_SetSRID(ST_MakePoint(93.2321, 21.0321))
or
'POINT(93.2321 21.0321)'::GEOMETRY(POINT, <SRID)
However:
PostGIS stores geometry with standard IEEE floating point precision, and checking 4 decimal places against 15 leaves plenty of room for misalignment.
Thus, you'd also want to make sure you are comparing precision in the same order of magnitude; use e.g. ST_Snap
or ST_SnapToGrid
to make sure two points are considered equal even if they differ after the 4th decimal place
Finally, the recommended way to compare PostGIS geometries is ST_Equals
.
SELECT *
FROM indsolv.address
WHERE ST_Equals(
a.location,
ST_Snap(
a.location,
ST_SetSRID(ST_MakePoint(93.2321, 21.0321), <srid>),
0.0001
)
)
;
Answered by geozelot on December 31, 2020
Refer:
SELECT a.* FROM indsolv.address a
WHERE a.location ~= point '(93.2321, 21.0321)';
... WHERE a.location ~= point '93.2321, 21.0321';
... WHERE a.location ~= '93.2321, 21.0321'::point;
... WHERE a.location ~= '(93.2321, 21.0321)'::point;
... WHERE a.location ~= cast('93.2321, 21.0321' AS point);
... WHERE a.location ~= cast('(93.2321, 21.0321)' AS point);
... WHERE a.location ~= '(93.2321, 21.0321)';
... WHERE a.location ~= '93.2321, 21.0321';
... WHERE a.location ~= point('93.2321, 21.0321)';
... WHERE a.location ~= point('(93.2321, 21.0321)');
... WHERE a.location ~= point('93.2321', '21.0321');
Answered by Thirumal on December 31, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP