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.
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!