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 PostGIS table with two geometry columns, both defined with SRID 4326. I can insert into the table without problem, using the following INSERT statement (where lng and lat are values passed in programmatically):

INSERT INTO pad_meta (
    uuid, created, updated, name, origin, radius, area, expiry, creator
) VALUES (
    $1, now(), now(), $2, ST_GeomFromText('POINT(lng, lat)', 4326), $3, 
    ST_Buffer(ST_GeomFromText('POINT(lng, lat)', 4326), $4), $5, $6
)

But when i query for an intersection using ST_Intersects, depending on the value of the point I get ERROR: Operation on mixed SRID geometries.

For example, this query works:

SELECT * FROM pad_meta where ST_Intersects(
    'POINT(-122.334172173172 46.602634395263560)'::geometry, area::geometry 
) ORDER BY created DESC;

And this errors out:

SELECT * FROM pad_meta where ST_Intersects(
    'POINT(-122.334172173172 47.602634395263560)'::geometry, area::geometry 
) ORDER BY created DESC;

Note, they are identical queries except the value of the longitude. I have experimented with different values, but not identified a clear transition point between the queries that work and don't.

I think I am fundamentally misunderstanding something. For the moment, I have solved/corrected/worked around the problem by re-formatting the query to use ST_GeomFromText and explicitly specifying the SRID:

SELECT * FROM pad_meta where ST_Intersects(
    ST_GeomFromText('POINT(-122.334172173172 47.602634395263560)', 4326), area
) ORDER BY created DESC;

But I honestly don't really understand what the difference is, or if this truly "the" solution.

My question is: Why am I getting an error only for specific values, and what is the proper way to format this query?

Here is my table definition for reference:

CREATE TABLE IF NOT EXISTS pad_meta (
  uuid CHAR(32),
  created TIMESTAMP,
  updated TIMESTAMP,
  name VARCHAR(128),
  origin GEOMETRY(Point, 4326),
  radius INTEGER,
  area GEOMETRY(Polygon, 4326),
  expiry TIMESTAMP,
  creator CHAR(32),
  PRIMARY KEY (uuid)
);

I have also verified that there is only one type of SRID in the geometry_columns:

SELECT f_table_name, f_geometry_column, srid FROM geometry_columns;
f_table_name | f_geometry_column | srid
--------------+-------------------+------
 pad_meta     | origin            | 4326
 pad_meta     | area              | 4326

Help/advice appreciated. Thank you! (Note: I have also seen this question, but since I am already explicitly defining my geometry SRIDs when inserting into the table, it seems like that is not what is happening.)

share|improve this question
add comment

2 Answers

When you specify a geometry without an SRID, it is actually 0 (or -1 for version < 2):

SELECT ST_SRID('POINT(-122.334172173172 46.602634395263560)'::geometry);
 st_srid
---------
       0

So when you use this geometry with another one with SRID=4326, it is mixing 0 and 4326. This is usually a useful error, if the spatial references are truly different. With your case, the SRIDs are the same, but you didn't encode the SRID to the query point. So, to fix your query always specify the same SRID for your query point, and they will no longer be mixed.

As a side note, the geography type has a hard-coded and implied SRID of 4326, as evidenced here:

SELECT ST_SRID('POINT(-122.334172173172 46.602634395263560)'::geography::geometry);
 st_srid
---------
    4326

So, if you use geography types instead of geometry types, the SRID does not need to be specified.


As to why one query has an error, and the other does not, ST_Intersects first does a && bounding box search, which is fast, and doesn't care about SRIDs. No mixed SRID error messages will be raised if the bounding boxes don't intersect. But if they do intersect, the second filter is _ST_Intersects, which is more precise and checks the two SRIDs to make sure they match, and raises an error if they are mixed. For example:

WITH pad_meta AS (
    SELECT 'SRID=4326;POLYGON((-124 50, -124 47, -121 50, -124 50))'::geometry AS area)

SELECT * FROM pad_meta where ST_Intersects(
    'POINT(-122.334172173172 46.602634395263560)'::geometry, area::geometry 
);

does not have any intersecting bounding boxes, and bypasses _ST_Intersects. But POINT(-122.334172173172 47.602634395263560) will raise the error because the bounding boxes do overlap (even though the geometries don't actually intersect).

However, with the same geometries and different filter:

WITH pad_meta AS (
    SELECT 'SRID=4326;POLYGON((-124 50, -124 47, -121 50, -124 50))'::geometry AS area)

SELECT * FROM pad_meta where _ST_Intersects(
    'POINT(-122.334172173172 46.602634395263560)'::geometry, area::geometry 
);

throws a mixed SRID error, because bounding boxes are not considered.

share|improve this answer
    
wow, thank you. that makes sense. is prefixing a query with SRID=4326 (as you did above) the right way to set the SRID for the rest of the statement? (as opposed to using ST_GeomFromText simply because I didn't know how else to specify the SRID...?) is there a way to set a default SRID for queries? seems rather verbose to explicitly set it each time. again, thanks! –  jessykate Aug 15 '13 at 6:06
1  
I've updated my answer to suggest using geography types, which are always 4326. Also, there are several ways to specify the SRID. –  Mike T Aug 15 '13 at 6:41
add comment

A couple of observations that may help: One, Point(Double, Double) is a native PostgreSQL function that you are coercing to a PostGIS data type. ST_MakePoint(double x, double y) will create proper geometry. Also, in your question you seem to refer to the second argument as longitude. The proper order is x, y, which corresponds to Longitude, Latitude. Getting those reversed can return unexpected results without throwing any exceptions.

None of this really explains why your first example works sometimes and not others, but hopefully this will help you create good queries.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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