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