-- From
-- + http://duspviz.mit.edu/tutorials/intro-postgis.php
-- + http://duspviz.mit.edu/web-map-workshop/leaflet_nodejs_postgis/
CREATE TABLE coffee_shops
(
id serial NOT NULL,
name character varying(50),
address character varying(50),
city character varying(50),
state character varying(50),
zip character varying(10),
lat numeric,
lon numeric
);
-- First in bash:
-- wget http://duspviz.mit.edu/_assets/data/intro-postgis-datasets.zip
-- unzip intro-postgis-datasets.zip
\copy coffee_shops FROM './samples/postgis/cambridge_coffee_shops.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE coffee_shops ADD COLUMN geom geometry(POINT,4326);
UPDATE coffee_shops SET geom = ST_SetSRID(ST_MakePoint(lon,lat), 4326);
create view coffee_shops_geojson as
SELECT row_to_json(fc) as data FROM (
SELECT 'FeatureCollection' As type, json_agg(f) As features
FROM (
SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((id, name)) As properties
FROM coffee_shops As lg
) AS f
) AS fc;
create or replace function "map.html"() returns text as $_$
select format($$
$$, (select data from coffee_shops_geojson));
$_$ language sql;