I have a table with geometry column called point, saved in SRID 4326. I want to get the geometry by its coordinates, but something didn't work. I suspect database configuration or something.

Here is what I do

select point from geom_data limit 1

And I get "0101000060E610000000000020558A5EC0000000C0F3B8424000B0CFCFA4717342"

This as text is

select AsText(point) from geom_data where point = '0101000060E610000000000020558A5EC0000000C0F3B8424000B0CFCFA4717342'

And the result is "POINT(-122.161445617676 37.444938659668)"

Trying this

select * from geom_data where ST_Contains(point, ST_GeomFromEWKT('SRID=4326;POINT(-122.161445617676 37.444938659668)'))

Didn't give a result. Tried this

select ST_PointFromText('POINT(-122.161445617676 37.444938659668)', 4326)

and get "0101000020E61000000F000020558A5EC0040000C0F3B84240" which is different.

Selecting within bounding box didn't worked either

select * from geom_data where
st_x(point) >= '-122.161445617670' AND st_x(point) <= '-122.161445617679'
AND st_y(point) >= '37.444938659660' AND st_y(point) <= '37.444938659669'

Any ideas?

  • You can and should use the tags to specify what GIS/database environment this pertains to, e.g. PostGIS.
    – blah238
    Jun 13, 2012 at 7:26
  • Didn't know that. Added tags. The database is version 8.4
    – kode
    Jun 13, 2012 at 8:10

3 Answers 3


To select all the points within a bounding box use the ST_Within function of PostGIS and create a bounding box with ST_MakeEnvelope:

SELECT * FROM geom_data WHERE ST_WITHIN(point, ST_MakeEnvelope(-122, 37, -121, 38, 4326));

In your case it is also possible to use the @ operator, that makes use of the geometry indexes (and thus it is faster), but only considers the bounding boxes of the geometries:

SELECT * FROM geom_data WHERE point @ ST_MakeEnvelope(-122, 37,-121, 38, 4326);
  • Ok, that is working, but if there a way to do it more precisely? I mean that specifying a bounding box can return 5 points (all points in the box). Is there a way to specify the exact point by its coordinates?
    – kode
    Jun 13, 2012 at 9:07
  • I don't exactly understand what you're trying to achieve. If you know already the coordinates of the point, why do you want to select it?
    – webrian
    Jun 13, 2012 at 11:42
  • To get other information from the table that is not recorded in the point object, but on the same row.
    – kode
    Jun 14, 2012 at 6:32

There are two parts to your question really. First, the binary discrepancy between the point in the database and the point you supply, and second, trying to do a spatial query on the points.

To address the discrepancy problem, it turns out that the point stored in your database is a 2D+M point. Running this query:

SELECT ST_AsEWKT('0101000060E610000000000020558A5EC0000000C0F3B8424000B0CFCFA4717342');


SRID=4326;POINTM(-122.161445617676 37.444938659668 1336176082171)

This is fine for spatial queries, because PostGIS will ignore the M parameter, which is used as a general per-vertex floating point attribute. Originally it stood for "measure", but it can represent anything you fancy.

The second problem can be solved in two ways. You can use the ST_Equals() function:

SELECT ST_Equals(ST_GeomFromEWKT('SRID=4326;POINTM(-122.161445617676 37.444938659668 1336176082171)'), ST_PointFromText('POINT(-122.161445617676 37.444938659668)', 4326));


SELECT ST_Equals(ST_AsEWKT('0101000060E610000000000020558A5EC0000000C0F3B8424000B0CFCFA4717342'), ST_PointFromText('POINT(-122.161445617676 37.444938659668)', 4326));

Both of which return true.

Or use ST_Within() if there is some tolerance needed:

SELECT ST_DWithin(ST_GeogFromText('SRID=4326;POINTM(-122.161445617676 37.444938659668 1336176082171)'), ST_GeogFromText('SRID=4326;POINT(-122.161445617676 37.444938659668)'), 1.0)

Notice here I'm using the geography type, this is so you can specify the distance in metres on unprojected data. If you just used the geometry type, you'd have to specify the tolerance in degrees, which is fraught and generally of not much use, or project the data first which is potentially inefficient.


Assuming that your table looks like

CREATE TABLE geom_data ( id serial );

-- i use 'point' for the name of the geometry column, but remember that 
-- a common practice is call it 'geom' or 'the_geom'
SELECT addgeometrycolumn ('geom_data', 'point', 4326, 'POINT', 2);

INSERT INTO geom_data (point) (SELECT ST_POINTFROMTEXT('POINT(-122.161445617676 37.444938659668)', 4326));

and you only need the coordinate x and the coordinate y of your points:

SELECT st_x(point), st_y(point) from geom_data;
  • What I want is the reverse operation by specifying the x and y coordinates to get the geometry (in my case point). There is a difference in the blob (see above) when creating point from text and that already saved in the database. I tried with adding z too (assume this is 0), but it didn't helped.
    – kode
    Jun 13, 2012 at 8:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.