acetwenty8/nyc_query.sql

## nyc_query.sql
###############################################################################################
############### SQL craziness to extract the data and load into output tables #################
###############################################################################################
ALTER TABLE joinways_input ALTER COLUMN way SET DATA TYPE geometry;
TRUNCATE joinways_input;
INSERT INTO joinways_input
  SELECT *
  FROM planet_osm_line
  WHERE (name IS NOT NULL) AND
        (
           (tunnel IS NOT NULL AND tunnel != 'no') OR
           (bridge IS NOT NULL AND bridge != 'no')
        );

DROP FUNCTION IF EXISTS sp_JoinWays();
CREATE OR REPLACE FUNCTION sp_JoinWays() RETURNS SETOF joinways_input AS $BODY$

  DECLARE curs1 REFCURSOR;
  DECLARE curs1_row RECORD;
  DECLARE dcount INTEGER;
  DECLARE a RECORD;
  DECLARE b RECORD;
  DECLARE c INTEGER;
  BEGIN

    CREATE TEMP TABLE joinways_single ON COMMIT DROP AS
      SELECT aa.* FROM joinways_input aa
      LEFT JOIN joinways_input bb
      ON aa.name=bb.name and aa.osm_id != bb.osm_id
      WHERE bb.name IS NULL
      AND aa.name IS NOT NULL;

    DELETE FROM joinways_input
    WHERE osm_id IN (SELECT osm_id FROM joinways_single);
    RETURN QUERY
      SELECT * FROM joinways_single;
    UPDATE joinways_input SET name='NO_NAME' WHERE name IS NULL;

    OPEN curs1 FOR
      SELECT name, COUNT(*) FROM joinways_input
      GROUP BY joinways_input.name
      ORDER BY COUNT(*) DESC;

    LOOP
      FETCH curs1 INTO curs1_row;
      IF NOT FOUND THEN
        EXIT;
      END IF;
      LOOP
         SELECT COUNT(*) FROM joinways_input WHERE name=curs1_row.name INTO c;
         IF c = 0 THEN
            EXIT;
         END IF;
         FOR a IN EXECUTE 'SELECT * FROM joinways_input WHERE name=$1 ORDER BY ST_Length(way) DESC;' USING curs1_row.name
         LOOP
            FOR b IN SELECT * FROM joinways_input WHERE joinways_input.name=curs1_row.name ORDER BY ST_Length(way) DESC
            LOOP
               IF ST_Touches(a.way,b.way) AND (ST_StartPoint(a.way)=ST_EndPoint(b.way) OR ST_StartPoint(b.way)=ST_EndPoint(a.way)) THEN
                  a.way = ST_LineMerge(ST_Union(a.way,b.way));
                  EXECUTE 'DELETE FROM joinways_input WHERE osm_id=$1' USING b.osm_id;
               END IF;
            END LOOP;
            EXECUTE 'DELETE FROM joinways_input WHERE joinways_input.osm_id=$1' USING a.osm_id;
            RETURN NEXT a;
            EXIT;
         END LOOP;
      END LOOP;
     END LOOP;

  RETURN;
  END $BODY$
  LANGUAGE 'plpgsql';

DROP TABLE IF EXISTS joinways_output;
CREATE TABLE joinways_output AS SELECT * from sp_JoinWays();
UPDATE joinways_output SET name=NULL WHERE name='NO_NAME';

DROP TABLE IF EXISTS distance_query;
CREATE TABLE distance_query AS
  SELECT
    a.osm_id,
    a.name,
    sum(st_length(st_transform(a.way,4326)::geography)*3.28084) AS length,
    a.tunnel,
    a.highway,
    a.bridge,
    a.ref,
    a.toll,
    a.railway,
    a.tags,
    a.way
  FROM joinways_output a
  GROUP BY a.osm_id, a.name, a.tunnel, a.highway, a.bridge, a.toll, a.ref, a.railway, a.waterway, a.tags, a.way
  ORDER BY length DESC;

CREATE TABLE nyc_query AS
  SELECT a.*
  FROM distance_query a
  JOIN planet_osm_polygon b
  ON st_within(st_startpoint(a.way),b.way) OR st_within(st_endpoint(a.way),b.way)
  WHERE b.admin_level = '5' AND b.name = 'New York City';

###############################################################################################
############################## PHP to build HTML result table #################################
###############################################################################################
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>nyc_query</title>
<link href="css/main-q.css" rel="stylesheet" type="text/css" />
<style type="text/css">
     #wrap {
      width: 3000px;
      margin: 0 ;
      background-color: #fff; }
</style>
</head>

<body>
<?php
#Connect to the database
$dbconn = pg_connect("host=172.16.100.6 port=5432 dbname=gis user=gis password=insecurepasswd");
if (!$dbconn) {
  echo "An error occurred connecting to the DB.\n";
  exit;
}
#Run the query
$result = pg_query($dbconn, "SELECT * FROM nyc_query;");
if (!$result) {
  echo "An error occurred running the query on the DB.\n";
  exit;
}

echo '<br />';
echo '<table cellpadding="0" cellspacing="0" width="100%">';
echo '<tr><td>';
echo '<table cellpadding="0" cellspacing="0" class="db-table">';
echo '<tr bgcolor="#A4D1FF"><td align="center" width="200">BRIDGE</td></tr>';
echo '<tr bgcolor="#ffd2a4"><td align="center" width="200">TUNNEL</td></tr>';
echo '</table><br />';
echo '</td><td><font size="3" color="#FFFFFF">NYC Bridge/Tunnel Length From Open Street Map Database</font><td></tr></table>';
echo '<table cellpadding="0" cellspacing="0" class="db-table">';
echo '<tr><th>name</th><th>length(ft)</th><th>highway</th><th>railway</th><th>ref</th><th>toll</th><th>tags</th></tr>';
echo '<font color ="#FFFFFF">Note: Bridge lengths may vary. Approach spans and ramps may or not be included in the calculation.</font>';

while ($array = pg_fetch_array($result, null, PGSQL_ASSOC)) {
  if(is_null($array['tunnel'])) {
    echo '<tr bgcolor="#A4D1FF">';
  }
  else {
    echo '<tr bgcolor="#ffd2a4">';
  }
  echo '<td align="left">'.$array['name'].'</td>';
  echo '<td align="left">'.round($array['length'], 2).'</td>';
  echo '<td align="left">'.$array['highway'].'</td>';
  echo '<td align="left">'.$array['railway'].'</td>';
  echo '<td align="left">'.$array['ref'].'</td>';
  echo '<td align="left">'.$array['toll'].'</td>';
  echo '<td align="left">'.$array['tags'].'</td>';
  echo '</tr>';
}


echo '</table>';

?>
</div>
</div>
</body>
</html>
	###############################################################################################
	############### SQL craziness to extract the data and load into output tables #################
	###############################################################################################
	ALTER TABLE joinways_input ALTER COLUMN way SET DATA TYPE geometry;
	TRUNCATE joinways_input;
	INSERT INTO joinways_input
	SELECT *
	FROM planet_osm_line
	WHERE (name IS NOT NULL) AND
	(
	(tunnel IS NOT NULL AND tunnel != 'no') OR
	(bridge IS NOT NULL AND bridge != 'no')
	);

	DROP FUNCTION IF EXISTS sp_JoinWays();
	CREATE OR REPLACE FUNCTION sp_JoinWays() RETURNS SETOF joinways_input AS $BODY$

	DECLARE curs1 REFCURSOR;
	DECLARE curs1_row RECORD;
	DECLARE dcount INTEGER;
	DECLARE a RECORD;
	DECLARE b RECORD;
	DECLARE c INTEGER;
	BEGIN

	CREATE TEMP TABLE joinways_single ON COMMIT DROP AS
	SELECT aa.* FROM joinways_input aa
	LEFT JOIN joinways_input bb
	ON aa.name=bb.name and aa.osm_id != bb.osm_id
	WHERE bb.name IS NULL
	AND aa.name IS NOT NULL;

	DELETE FROM joinways_input
	WHERE osm_id IN (SELECT osm_id FROM joinways_single);
	RETURN QUERY
	SELECT * FROM joinways_single;
	UPDATE joinways_input SET name='NO_NAME' WHERE name IS NULL;

	OPEN curs1 FOR
	SELECT name, COUNT(*) FROM joinways_input
	GROUP BY joinways_input.name
	ORDER BY COUNT(*) DESC;

	LOOP
	FETCH curs1 INTO curs1_row;
	IF NOT FOUND THEN
	EXIT;
	END IF;
	LOOP
	SELECT COUNT(*) FROM joinways_input WHERE name=curs1_row.name INTO c;
	IF c = 0 THEN
	EXIT;
	END IF;
	FOR a IN EXECUTE 'SELECT * FROM joinways_input WHERE name=$1 ORDER BY ST_Length(way) DESC;' USING curs1_row.name
	LOOP
	FOR b IN SELECT * FROM joinways_input WHERE joinways_input.name=curs1_row.name ORDER BY ST_Length(way) DESC
	LOOP
	IF ST_Touches(a.way,b.way) AND (ST_StartPoint(a.way)=ST_EndPoint(b.way) OR ST_StartPoint(b.way)=ST_EndPoint(a.way)) THEN
	a.way = ST_LineMerge(ST_Union(a.way,b.way));
	EXECUTE 'DELETE FROM joinways_input WHERE osm_id=$1' USING b.osm_id;
	END IF;
	END LOOP;
	EXECUTE 'DELETE FROM joinways_input WHERE joinways_input.osm_id=$1' USING a.osm_id;
	RETURN NEXT a;
	EXIT;
	END LOOP;
	END LOOP;
	END LOOP;

	RETURN;
	END $BODY$
	LANGUAGE 'plpgsql';

	DROP TABLE IF EXISTS joinways_output;
	CREATE TABLE joinways_output AS SELECT * from sp_JoinWays();
	UPDATE joinways_output SET name=NULL WHERE name='NO_NAME';

	DROP TABLE IF EXISTS distance_query;
	CREATE TABLE distance_query AS
	SELECT
	a.osm_id,
	a.name,
	sum(st_length(st_transform(a.way,4326)::geography)*3.28084) AS length,
	a.tunnel,
	a.highway,
	a.bridge,
	a.ref,
	a.toll,
	a.railway,
	a.tags,
	a.way
	FROM joinways_output a
	GROUP BY a.osm_id, a.name, a.tunnel, a.highway, a.bridge, a.toll, a.ref, a.railway, a.waterway, a.tags, a.way
	ORDER BY length DESC;

	CREATE TABLE nyc_query AS
	SELECT a.*
	FROM distance_query a
	JOIN planet_osm_polygon b
	ON st_within(st_startpoint(a.way),b.way) OR st_within(st_endpoint(a.way),b.way)
	WHERE b.admin_level = '5' AND b.name = 'New York City';

	###############################################################################################
	############################## PHP to build HTML result table #################################
	###############################################################################################
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>nyc_query</title>
	<link href="css/main-q.css" rel="stylesheet" type="text/css" />
	<style type="text/css">
	#wrap {
	width: 3000px;
	margin: 0 ;
	background-color: #fff; }
	</style>
	</head>

	<body>
	<?php
	#Connect to the database
	$dbconn = pg_connect("host=172.16.100.6 port=5432 dbname=gis user=gis password=insecurepasswd");
	if (!$dbconn) {
	echo "An error occurred connecting to the DB.\n";
	exit;
	}
	#Run the query
	$result = pg_query($dbconn, "SELECT * FROM nyc_query;");
	if (!$result) {
	echo "An error occurred running the query on the DB.\n";
	exit;
	}

	echo '<br />';
	echo '<table cellpadding="0" cellspacing="0" width="100%">';
	echo '<tr><td>';
	echo '<table cellpadding="0" cellspacing="0" class="db-table">';
	echo '<tr bgcolor="#A4D1FF"><td align="center" width="200">BRIDGE</td></tr>';
	echo '<tr bgcolor="#ffd2a4"><td align="center" width="200">TUNNEL</td></tr>';
	echo '</table><br />';
	echo '</td><td><font size="3" color="#FFFFFF">NYC Bridge/Tunnel Length From Open Street Map Database</font><td></tr></table>';
	echo '<table cellpadding="0" cellspacing="0" class="db-table">';
	echo '<tr><th>name</th><th>length(ft)</th><th>highway</th><th>railway</th><th>ref</th><th>toll</th><th>tags</th></tr>';
	echo '<font color ="#FFFFFF">Note: Bridge lengths may vary. Approach spans and ramps may or not be included in the calculation.</font>';

	while ($array = pg_fetch_array($result, null, PGSQL_ASSOC)) {
	if(is_null($array['tunnel'])) {
	echo '<tr bgcolor="#A4D1FF">';
	}
	else {
	echo '<tr bgcolor="#ffd2a4">';
	}
	echo '<td align="left">'.$array['name'].'</td>';
	echo '<td align="left">'.round($array['length'], 2).'</td>';
	echo '<td align="left">'.$array['highway'].'</td>';
	echo '<td align="left">'.$array['railway'].'</td>';
	echo '<td align="left">'.$array['ref'].'</td>';
	echo '<td align="left">'.$array['toll'].'</td>';
	echo '<td align="left">'.$array['tags'].'</td>';
	echo '</tr>';
	}


	echo '</table>';

	?>
	</div>
	</div>
	</body>
	</html>