Monday, 1 October 2018

PostGIS Geometry Query returns "Error: Operation on mixed SRID geometries" only for certain values


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.


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


I have also seen Why does only one of the following queries throw an error?, but since I am already explicitly defining my geometry SRIDs when inserting into the table, it seems like that is not what is happening.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...