geonames_dbscan.c File Reference

A simple program that reads from a CSV file obtained from geonames https://download.geonames.org/export/dump/US.zip and applies the PostGIS function ST_DBSCAN 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	MAX_LINE_LENGTH   2048
#define	MAX_NO_ADMIN1   64
#define	MAX_NO_FIELDS   24
#define	MAX_ROWS   250000
#define	MAX_ROWS_WIN   15000
#define	NO_INSTANTS_BATCH   1000

Functions
int	get_admin1_no (char *admin1, char *admin1_list[])
int	main (void)
int	parse_csv_line (char *line, char **fields)
char *	trim (char *str)

Detailed Description

A simple program that reads from a CSV file obtained from geonames https://download.geonames.org/export/dump/US.zip and applies the PostGIS function ST_DBSCAN to the entire file.

This file simply reproduces in MEOS Paul Ramsey's blog https://www.crunchydata.com/blog/postgis-clustering-with-k-means Therefore, the program corresponds to the following SQL query

DROP TABLE IF EXISTS geonames_sch;
CREATE TABLE geonames_sch AS
SELECT *, ST_ClusterDBScan(geom, 2000, 5)
  OVER (PARTITION BY admin1) AS cluster
FROM geonames
WHERE fcode = 'SCH';

The program can be build as follows

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

The program expects that the file data/US.txt extracted from the ZIP file above is located in the subdirecttory ‘data’.

DROP TABLE IF EXISTS geonames_meos;
CREATE TABLE geonames_meos (
  geonameid int,
  name text,
  admin1 text,
  geom geometry(Point, 5070),
  cluster integer);
COPY geonames_meos
  FROM '/home/esteban/src/MobilityDB/meos/examples/data/geonames_new.csv'
  DELIMITER ',' CSV HEADER;