Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

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

3 Answers 3

up vote 2 down vote accepted

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');

Yields:

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));

or

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.

share|improve this answer
    
Thanks, this is what i want to know. –  kode Oct 9 '12 at 8:09

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);
share|improve this answer
    
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 '12 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 '12 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 '12 at 6:32

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;
share|improve this answer
    
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 '12 at 8:46

protected by whuber Jul 23 '12 at 12:14

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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