I have a layer of points and want to join some data from polygons where the two coincide. I might have several points in one poly.

The points were generated from a CSV as;

UPDATE t_osab_with_voa_ndr SET geom_point_4258 = ST_SetSrid(ST_MakePoint(longitude, latitude),4258);

Where they are stated as being ETRS89 projection which is SRID 4258

I'm running into problems as the points have SRID 4258 and the Polys are 27700 I've tried using ;

ALTER TABLE t_osab_with_voa_ndr ALTER COLUMN geom_point_4258 TYPE geometry(point,27700) USING ST_Transform(geom_point_4258,27700);

When I look in QGIS I see this;

enter image description here

But when I run;

create or replace view v_spatialjoin_northeast_st_within1 as select * from t_osab_with_voa_ndr , d_geom_northeast where st_within(t_osab_with_voa_ndr.geom_point, d_geom_northeast.geom);

I get no results when I can see I should

Question 1. Can i combine the ST_Within / ST_Interects and specify the SRID in the process or am i better transforming the points first?

EDIT: Reason for my blanks was SQL problem with ST_Within, but still interested to know if you can set SRID or what is the fastest way to execute this

  • shouldn't you transform geom_point_4258? – Ian Turton Apr 26 '18 at 10:16
  • @IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error – mapping dom Apr 26 '18 at 10:20
  • @ThingumaBob good spot but still get no results – mapping dom Apr 26 '18 at 10:30
  • You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table. – John Powell Apr 26 '18 at 10:48
  • @JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere – mapping dom Apr 26 '18 at 10:50

The reason for the blanks in this instance was due to my query, what worked was rewriting this as below, code taken from good blog post here

SELECT pts.*, blocks.inspireid as inspire_poly_id FROM t_osab_with_voa_ndr AS pts INNER JOIN d_geom_northeast AS blocks ON st_within(pts.geom_point, blocks.geom);

  • ...actually, that's weird since, although the (INNER) JOIN ... ON ... should be faster here than (and the preferred style in general in favour to) a CROSS JOIN ... WHERE ..., in the end it should give the same reuslts... – ThingumaBob Apr 26 '18 at 11:12
  • I'm running the same process in QGIS to double check my code.. – mapping dom Apr 26 '18 at 12:14

For me what works is to use the ST_transforn instead of ST_Set SRID in order to change the SPATIAL REFERENCE SYSTEM. After that the SPATIAL query wtih INNER JOIN or the other one works perfectly. Here is the explanation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.