1

I am a new user in PostGIS. I need to create a table with the ID_DIR of table A, ID_SQ of table B and have a new column called "Distance" that would be the distance between the ID_DIR and the ID_SQ in a zone of influence of 500 meters of each ID_SQ. enter image description here

CREATE TABLE RESULT AS (
   SELECT a.ID_DIR,
          ST_AsTEXT(ST_Shortesline(a.geom,b.geom) AS geom,
          a.ID_SQ, 
          ST_Length(
            St_Shortesline(a.geom,b.geom)::geography/1000) AS Distance 
   FROM TABLE_A AS a, 
        TABLE_B AS b 
   WHERE ST_Dwithin (a.geom,b.geom,500) 
   ORDER BY a.ID_DIR,Distance ASC
);

I have two problems: 1) This query does not work 2) I have seen that the processing time in these examples is very high. Table A has 4 million data and it is duplicated depending on the times it crosses with the ID_SQ buffer Help!

  • 2
    Please expand "query does not work" - are you getting errors (specify details), or unexpected results (again details), query runs forever, etc? – Michael May 10 at 16:36
  • 1
    in addition to @JGH's answer, and since you are new to PostGIS: it's essential to have a spatial index in place on (at least one, but better go for) both geometry columns. if you go with the on-the-fly cast to GEOGRAPHY (instead of projecting or a type change), I'd suggest to use a functional index like ... USING GIST (CAST(geom AS GEOGRAPHY)); – ThingumaBob May 11 at 8:55
1

The two points are in 4326, so the where condition WHERE ST_Dwithin (a.geom,b.geom,500) is looking for points within 500 degrees of each others.

You could also cast to geography here

WHERE ST_Dwithin (a.geom::geography,b.geom::geography,500) 

Also you don't need the length + shortestline. Computing the distance directly will give the proper result

SELECT ST_DISTANCE(a.geom::geography,b.geom::geography)  as distance_m

While it won't affect much the speed, there is no need to change a geometry to text to later cast it back as geometry. I suggest removing st_asText

  • Thank you! it works! – Marcelo Esteban Lagos Bustaman Jul 2 at 21:43
  • @MarceloEstebanLagosBustaman Great! Please consider marking this answer as accepted (green check mark to the left) so others know there is a working solution – JGH Jul 3 at 1:48

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.