I have a table containing the street network of Chicago, and I also have a table of crimes committed in Chicago. I am trying to to create k-means clusters for the crimes by assigning them to the cluster centre that is the shortest road distance away.
Firstly, I interpolated all the crimes onto the closest road. So far, so good. Now what I'm trying to do is to split each road by all the crime points that fall on it, so that I can then create a network topology using pgrouting and route from one crime location to another.
Problem is, the ST_Split function does not seem to be splitting most of the roads and I have no idea why. Given I have a million crime points, the roads should be split into a large amount of segments, but I'm only getting about a thousand rows more than there are in the original street network table. This is the command I'm using:
CREATE TABLE algorithms.crime_network AS
SELECT road.id AS road_id, (ST_Dump(ST_Split(road.geom, road.crime_points))).geom
FROM (SELECT r.geom as geom, r.gid id, ST_Multi(ST_Collect(c.geom)) AS crime_points FROM public.transportation r INNER JOIN chicago_data.interpolated_crimes c ON c.road_id = r.gid GROUP BY r.gid) AS road;
I am using Postgis version 2.2.2 so the fact that I'm splitting by multipoints isn't a problem..
Any help would be appreciated!
As commented, some functions like all of the overlay operators and ST_Split require exact noding to perform as expected. This means that annoying floating point differences of geometry overlays will have vertexes from different geometries very close to each other (on the order of <1e-12), but not exact.
Use ST_Snap to get exact noding of one geometry on another, which will help functions like ST_Split operate as expected.