regions_intersecting.c File Reference

A simple program that reads from the regions.csv file obtained from the BerlinMOD benchmark https://github.com/MobilityDB/MobilityDB-BerlinMOD and applies one of the PostGIS functions ST_ClusterIntersecting or ST_ClusterWithin to the entire file. More...

#include <ctype.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <meos.h>
#include <meos_geo.h>

Macros
#define	CLUSTER_INTERSECTING   false
#define	MAX_LEN_LINE   4096
#define	MAX_ROWS   100

Functions
int	main (void)

Detailed Description

A simple program that reads from the regions.csv file obtained from the BerlinMOD benchmark https://github.com/MobilityDB/MobilityDB-BerlinMOD and applies one of the PostGIS functions ST_ClusterIntersecting or ST_ClusterWithin to the entire file.

The program corresponds to one of the following SQL queries

DROP TABLE IF EXISTS RegionsIntersecting;
CREATE TABLE RegionsIntersecting AS
WITH Temp1 AS (
  SELECT unnest(ST_ClusterIntersecting(geom)) geom FROM Regions ),
Temp2 AS (
  SELECT ROW_NUMBER() OVER () AS cluster, ST_Dump(geom) AS rec FROM Temp1 )
SELECT cluster, (rec).geom FROM Temp2;

or

DROP TABLE IF EXISTS RegionsWithin;
CREATE TABLE RegionsWithin AS
WITH Temp1 AS (
  SELECT unnest(ST_ClusterWithin(geom, 1000)) geom FROM Regions ),
Temp2 AS (
  SELECT ROW_NUMBER() OVER () AS cluster, ST_Dump(geom) AS rec FROM Temp1 )
SELECT cluster, (rec).geom FROM Temp2;

The program can be build as follows

gcc -Wall -g -I/usr/local/include -o regions_intersecting regions_intersecting.c -L/usr/local/lib -lmeos

The program expects the file data/regions.csv and produces a new file data/regions_new.csv with a new column cluster added to each geometry. This file can be input in PostgreSQL using the following SQL command, after which it can be visualized with QGIS.

DROP TABLE IF EXISTS regions_meos;
CREATE TABLE regions_meos (
  regionid int,
  geom geometry,
  cluster integer);
COPY regions_meos
  FROM '/home/esteban/src/MobilityDB/meos/examples/data/regions_new.csv'
  DELIMITER ',' CSV HEADER;