Jittering GeoJSON points

Problem: You have geometry points that have the same (or nearly same) lat and lon in your GeoJSON/shp file and when you map them they overlap.

Solution: Using Leaflet... You can use the markercluster plugin and spiderfy the cluster. Using MapboxGL... you'll have to add random jitter (slightly change the lat and lon of the overlapping points). This process will take you through how to do that using PostGIS and QGIS.

Get data into PostGIS
You can use the following SQL query to determine what points will need to be jittered. This will depend on what your fields are. In my dataset, some points had the exact same geom (lat/lon) while other had a slightly different lat/lon but the same address. In both cases, I wanted to jitter these points.

-- Find rows that need to be jittered  
SELECT DISTINCT a.*
FROM
    your_table as a,
    your_table as b
WHERE
    (ST_Intersects(a.geom, b.geom)
      AND a.gid != b.gid)
    OR
    (a.street = b.street
      AND a.gid != b.gid);

Once you're satisfied with the points that the above query yields, you'll want to create a new table with new geometry for those points.

Here we use the PostGIS function ST_Translate() which takes parameters of the geometry column, Δlat, Δlon. We are going to randomize how much we change the jittered points, but first we have to pick a range to do it in.

My data is in degrees, so I decided to alter the lat and lon by a minimum of 0.00005 deg and a maximum of 0.0001 degrees. In order to pick a random number within that range, we use the SQL random() function like this:

random() * (max - min) + min

And now we can create another (temporary) table solely containing the points that needed to be jittered, with whatever columns you'd like to bring over, adding the new geometry. Note: I had problems exporting this table into a SHP or GeoJSON file from QGIS if it had more than one geometry column.

-- Create new table with only jittered points
WITH points_to_be_jittered AS (
  SELECT DISTINCT a.*
  FROM
      your_table as a,
      your_table as b
  WHERE
      (ST_Intersects(a.geom, b.geom)
        AND a.gid != b.gid)
      OR
      (a.street = b.street
        AND a.gid != b.gid);

SELECT gid, other_columns,
  ST_Translate(geom, random()*(0.0001-0.00005)+0.00005, random()*(0.0001-0.00005)+0.00005) as geom
INTO your_table_jittered
FROM your_table
WHERE gid IN (SELECT gid FROM points_to_be_jittered);

At this point, you now have a temporary table that you can check to see if this worked. You can get it into QGIS to make sure the points are very close - but no longer touching - if you'd like.

Create one final table with one geom column

We'll use SQL's COALESCE() function to first find if the point has a new jittered geometry or if we should use the original.

-- Create new final table with one geom column
  SELECT a.gid, other_columns, COALESCE(b.geom, a.geom) as geom                 
  INTO new_table
  FROM your_table a
  LEFT JOIN your_table_jittered b ON a.gid = b.gid;

From here, I opened up the new table as a layer in QGIS and then save the layer as a GeoJSON or shapefile. Under the "Geometry" section in the save form, select "Point" from the dropdown instead of "Automatic" then save the file.

arianagiorgi/README.md

Jittering GeoJSON points