I have multiple tables in a PSQL/PostGIS database which are comprised of GPS locations of animals. The tables are uploaded into the database with latitude, longitude, and timestamp columns, and then I create geometry columns from the latitude/longitude columns as follows:
ALTER TABLE main.gps_animals_data ADD COLUMN geom geometry; UPDATE main.gps_animals_data SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 22234);
In this way, I assumed my SRID was set to the EPSG projection 22234, which is the UTM projection in my study area. However, when I try to do a simple spatial join between tables, I get a "mixed geometries" error (which I know means the tables don't share the same projection). I then try
SELECT Find_SRID('main', 'gps_animals_data', 'geom') , and it gives me 0.
I've tried updating my geometry column such as:
UPDATE main.gps_animals_data SET geom = ST_SetSRID(geom, 22234);
as well as creating a new geometry column from the coordinates (again) but assigning them an 4326 SRID (in case there's something wrong with 22234 for whatever reason), and I've also tried modifying my spatial join query to explicitly assign an SRID, such as:
SELECT ST_Distance( ST_SetSRID(ST_MakePoint(t1.longitude, t1.latitude), 4326), ST_SetSRID(geom_wgs84, 4326)) distance_apart
No matter what, I keep getting:
ERROR: Operation on mixed SRID geometries CONTEXT: parallel worker
Is there a reason that my tables are not storing the SRID's that I assign them, or are my commands not correct?
ST_SetSRIDon a stored column (more than once). If it's been set earlier to other than 4326, you need to
ST_Transformto 4326. Also, to get geodesic distance, you need to cast both to
geography. And your
SELECTis missing a
FROMin your code block.