CartoDB example


Attempting to perform a rudimentary spatial join of 2 data sources, via CSV, to cloud db storage....

 

Spatial query against lgpn extract

 

Query creates a JSON return of all data from where the location intersects with a 10 degree buffer of 25.81, 36.81

 

https://kcl-ddh.cartodb.com/api/v1/sql?q=SELECT * from lgpn where st_intersects(the_geom,st_setsrid(ST_Buffer(ST_makepoint(25.81,36.81),1),4326))

 

 

Spatial query of 2 datasets:

 

Queries lgpn as above and UNIONS that result with a slightly larger buffer for the coins data....

 

http://kcl-ddh.cartodb.com/api/v1/sql?q=SELECT 'lgpn',name_uc from lgpn
where st_intersects(the_geom,st_setsrid(ST_Buffer(ST_makepoint(25.81,36.81),1),4326))
union all
select 'coins',field_1 from coins where st_intersects(the_geom,st_setsrid(ST_Buffer(ST_makepoint(25.81,36.81),5),4326))

 

 

 

A Spatial query of the lgpn subset where date between 230 and 250 AD:

 

http://kcl-ddh.cartodb.com/api/v1/sql?q=SELECT * FROM lgpn where notafter::integer < 250 and notbefore::integer > (230)

 

 

Also available as TMS (Tile Service) for easy incorporation into OpenLayers, Google Maps etc...

 

http://kcl-ddh.cartodb.com/tiles/lgpn /{zoom}/{x}/{y}.png?sql=SELECT * from table_name etc etc