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
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.