Operation on mixed SRID geometries error

Question

I have two tables flickr_edin and temmpat.

temppat was created using:

CREATE TABLE temppat (id serial, geom geometry);
INSERT INTO temppat (geom) VALUES (ST_GeomFromText('POINT (326253.33 674110.63)'));

flickr_edin contains columns id, date_taken, user and geom.

I want to find out how many photos have been taken within 200m of this location in temmpat for each day of the week.

This is my query:

SELECT COUNT(*) as count,EXTRACT('dow' FROM date_taken) as dow
FROM flickr_edin a JOIN temppat b ON ST_WITHIN(a.geom,ST_BUFFER(b.geom,200))
GROUP BY dow;

I get this error:

ERROR: contains: Operation on mixed SRID geometries (Polygon, 0) != (Point, 27700) SQL state: XX000

Answer 1

An inline SQL test to demonstrate what goes wrong and how to fix the problem.

select ST_Within(
ST_GeomFromText('POINT (326253.33 674110.63)'),
ST_Buffer(ST_GeomFromText('POINT (326253.33 674110.63)',27700),10));

ERROR:  contains: Operation on mixed SRID geometries
(Polygon, 27700) != (Point, 0)
SQL state: XX000


select ST_Within(
ST_GeomFromText('POINT (326253.33 674110.63)',27700),
ST_Buffer(ST_GeomFromText('POINT (326253.33 674110.63)',27700),10));

true

Answer 2

You need to specify the SRID of the table you create. To create it with the same SRID as the flickr_edin table:

CREATE TABLE temppat (id serial, geom geometry(Geometry,27700));
INSERT INTO temppat (geom) VALUES (ST_GeomFromText('POINT (326253.33 674110.63)'));

To find out the SRID(s) of a table:

SELECT DISTINCT ON (ST_SRID(geom)) ST_SRID(geom) FROM flickr_edin

To set the SRID of a geom column that has already been created:

UPDATE temppat SET geom = ST_SETSRID(geom,27700)

Note that this won't transform the geometries. For that, see this question

You can also set the SRID on the fly, but it's better to update the table itself if you're dealing with a missing SRID for a column. Setting SRID on the fly enables you to do spatial operations on two tables that have different SRIDs. To do it on the fly, using your query:

SELECT COUNT(*) as count,EXTRACT('dow' FROM date_taken) as dow
FROM flickr_edin a JOIN temppat b ON ST_WITHIN(a.geom,ST_BUFFER(ST_SETSRID(b.geom,27700),200))
GROUP BY dow;