I'm working on a project to connect buildings to electric power plant via a network. To this end I'm beginning by trying to connect every buildings to the network.
I've importing the shp files to a database via shp2pgsql and now I've got a table of MultiPoints (buildings, with p the number of rows) and another one of MultiLineString (network, with n the number of rows).
I used ST_ClosestPoint and ST_ShortestLine on these tables but I'm facing the same problem with both of them : it returns a table of n x p rows because it computes the closest point (or the shortest line) from each point to each line of the network. My goal is to find the closest point on any line of the network for each building.
Right now the best I've got is this query :
SELECT min(ST_Length(ST_ShortestLine(network.geom, buildings.geom))) as length, id FROM network, building group by id
It returns a table of the length of the shortest line for each building but I can't find any way to get the geometry of the concern line (in order to know the location of the point on the network).
I could do a sub-query to do it point by point, order the n rows by calculing the length of each line and keep the one with the shortest length but the final goal is to use this query on files with over 200 000 buildings and a network of over 50 000 lines so I think I need to find the most efficient way.
Moreover, I did not find any way to find the "closest closest point" using the result of the query ST_ClosestPoints(network, buildings).
Does anyone think of a way of doing this efficiently ?
Thanks ahead,
