Skip to content

Instantly share code, notes, and snippets.

@steve-chavez

steve-chavez/map.sql

Last active January 31, 2022 02:25
Show Gist options
  • Save steve-chavez/c1435a8c9583d2524e87e4f7c378d322 to your computer and use it in GitHub Desktop.
Save steve-chavez/c1435a8c9583d2524e87e4f7c378d322 to your computer and use it in GitHub Desktop.
Download ZIP
Create a OSM map from a pg function with PostgREST
Raw
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/[email protected]/dist/leaflet.css">
<script src="https://unpkg.com/[email protected]/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;
Raw
postgrest.conf
db-uri=".."
db-schema="public"
db-anon-role="postgres"
raw-media-types="text/html"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment