TransWikia.com

Selecting from Point column in PostgreSQL?

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

2 Answers

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP