steve-chavez/map.sql

## map.sql
-- 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($$
<!DOCTYPE html>
<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title></title>
  <link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.1/dist/leaflet.css">
  <script src="https://unpkg.com/leaflet@1.3.1/dist/leaflet.js"/></script>
  <style>
  #map {
    width: 100%%;
    height: 600px;
  }
  </style>
</head>
<body>

<div id="map"></div>

<script id="data" type="application/json">
%s
</script>

<script defer>
var myData = JSON.parse(document.querySelector('#data').innerHTML);
var map = L.map("map",{ center: [42.375562, -71.106870], zoom: 14});
L.tileLayer("http://{s}.tile.osm.org/{z}/{x}/{y}.png", {
  attribution: "© OpenStreetMap"
}).addTo(map);
L.geoJson(myData,{
    onEachFeature: function (feature, layer) {
        layer.bindPopup(feature.properties.f2);
    }
}).addTo(map);
</script>

</body>
</html>
$$, (select data from coffee_shops_geojson));
$_$ language sql;

## postgrest.conf
db-uri=".."
db-schema="public"
db-anon-role="postgres"
raw-media-types="text/html"
	-- 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($$
	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title></title>
	<link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.1/dist/leaflet.css">
	<script src="https://unpkg.com/leaflet@1.3.1/dist/leaflet.js"/></script>
	<style>
	#map {
	width: 100%%;
	height: 600px;
	}
	</style>
	</head>
	<body>

	<div id="map"></div>

	<script id="data" type="application/json">
	%s
	</script>

	<script defer>
	var myData = JSON.parse(document.querySelector('#data').innerHTML);
	var map = L.map("map",{ center: [42.375562, -71.106870], zoom: 14});
	L.tileLayer("http://{s}.tile.osm.org/{z}/{x}/{y}.png", {
	attribution: "© OpenStreetMap"
	}).addTo(map);
	L.geoJson(myData,{
	onEachFeature: function (feature, layer) {
	layer.bindPopup(feature.properties.f2);
	}
	}).addTo(map);
	</script>

	</body>
	</html>
	$$, (select data from coffee_shops_geojson));
	$_$ language sql;
	db-uri=".."
	db-schema="public"
	db-anon-role="postgres"
	raw-media-types="text/html"