Building an online tool for spatial joins

greenbeansneedlesSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

92 views

Building an online tool for spatial joins


using open source software


Karsten Vennemann




Seattle


Building an online tool for spatial joins

-

-

-

-

Why this tool ?


What does it do ?


-

-

-

-

Steps to retrieve results



-

-

-

-

Tool live demo



-

-

-

-

How does it work ?



-

-

-

-

How can you build your own ?

Talk Overview





Who, Why and What





Tool Workflow





Definitions and Examples





Technical Background





Resources

Building an online tool for spatial joins

Why this online tool ? Why open source
software ?


Finding the Legal District in which a member of an organization lives is
important information for many environmental voter advocacy groups. While
this easily can be accomplished using commercial GIS tools, most of those
Non
-
Profit organizations lack the resources and/or technical staff to support the
use of such software.


Adding Legislative District information is
usually done manually.

In Washington State there is the online
district finder tool available:
http://apps.leg.wa.gov/

DistrictFinder/Default.aspx


Can find single addresses

Who, Why and What


Building an online tool for spatial joins

About this tool




Windows and Linux server platforms




based on open source software




any type of spatial join


based on street address in WA state (US) and a polygon layer

For simplicity we will focus on a technical overview on

how a web based tool can allow users

with little or no GIS knowledge to attach legislative district
information for Washington State

to their membership lists.


Who, Why and What


Building an online tool for spatial joins

Workflow for the tool can be broken up into four main parts



1)
File upload to the server

(text or Excel file containing address list via web interface

)


2)
Geocoding

-

Retrieval of coordinates (latitude/longitude) for the address

(geocoded using 2006 Tiger line data, PHP and Perl)




3)
Spatial Join
-

Matching the Legislative District information

(Query onto a PostGIS Database)


4) Transfer result information

(streaming the results to the client computer text or MS Excel file)

Tool Workflow

Building an online tool for spatial joins

Workflow for the tool can be broken up into four main parts



1)
File upload to the server

(text or Excel file via web interface )


2)
Geocoding

-

Retrieval of coordinates (latitude/longitude) for the address

(geocoded using 2006 Tiger line data, PHP and Perl)




3)
Spatial Join
-

Matching the Legislative District information

(Query onto a PostGIS Database
)


4) Transfer result information

(streaming the results to the client computer text or MS Excel file)

Tool Workflow

Building an online tool for spatial joins

What is Geocoding ?


Interpolation of real world coordinates for a given location


Most geocoders use address information to interpolate (estimate) the
real world position along streets using blocks and intersections as
landmarks


Variety of free tools available
-

most popular:

Google and Yahoo maps API, geocoder.us

Also Tiger line geocoder for Post GIS


=> Geocoder::US a Perl module

Open source

can download source code and install on your own server

uses Berkeley DB format to store Tiger data

-

high compression fro data

-

whole US Street data 750 MB (24 GB of uncompressed Tiger data)

Definitions and Examples

Building an online tool for spatial joins

What is a Spatial Join ?

A spatial join operation adds information based on the geographical
location of one feature to another


ID

precinct

352

BEL 48
-
2776

Definitions and Examples

Building an online tool for spatial joins

Spatial Join

A spatial join operation adds information based on the geographical location of
one feature to another


ID

address

223428

16089 Northrup Way

ID

precinct

352

BEL 48
-
2776

Definitions and Examples


Building an online tool for spatial joins

Spatial Join

A spatial join operation adds information based on the geographical location of
one feature to another


ID

address

223428

16089 Northrup Way

ID

precinct

352

BEL 48
-
2776

ID

address

precinct

223428

16089 Northrup Way

BEL 48
-
2776

Definitions and Examples


Building an online tool for spatial joins

Spatial Join

Definitions and Examples



Building an online tool for spatial joins

Spatial Join

Definitions and Examples


Building an online tool for spatial joins

Live Tool Demo

Definitions and Examples


Building an online tool for spatial joins

Live Tool Demo

Definitions and Examples


Building an online tool for spatial joins

Live Tool Demo

Definitions and Examples


Building an online tool for spatial joins

Live Tool Demo

Definitions and Examples


Building an online tool for spatial joins

Live Tool Demo

Definitions and Examples


Building an online tool for spatial joins

Technical

Overview


Server
Side
Apache II
`
Client
Side
www
http
Browser
HTML Form
PHP
php_perl
php_pgsql
Perl
Geo::Coder::US
SQL
Upload Directory:
membership file
Berkeley DB
2006 Tiger
Line Data
WA State
PostGIS DB
spatial layer
Legal Districts WA
upload file
stream results
text or Excel file
Technical Background

Building an online tool for spatial joins

Technical Overview


1.
Text or Excel file is read with php

2.
Get user input to select address fields

3.
Address is concatenated and feed into the geocoder::US Perl module

from within php

4.
lat/long returned

5.
Spatial query using lat/long results from geocoding

=> in Post GIS one simple SQL query is called to retrieve the LD number

6.
Output of results


Sample sql queries



select ndistrict from Leg_districts_2001 where Leg_districts_2001.the_geom &&
(setsrid((MakePoint(
-
122.206834, 47.611421)),4326)) and intersects
(Leg_districts_2001.the_geom,setsrid((MakePoint(
-
122.206834, 47.611421)),4326));



select ndistrict from Leg_districts_2001 where Leg_districts_2001.the_geom &&
transform(((setsrid((MakePoint(
-
122.206834, 47.611421)),4326))),2285) and intersects
(Leg_districts_2001.the_geom,transform(((setsrid((MakePoint(
-
122.206834,
47.611421)),4326))),2285));

Technical Background

Building an online tool for spatial joins

Code

Technical Background

Building an online tool for spatial joins

Perl integration for Geo::Coder::US

Technical Background

Building an online tool for spatial joins

Query in PostGIS

Technical Background

Building an online tool for spatial joins

Source: “Introduction to PostGIS”, Paul Ramsey, Refractions Research

Spatial Indexes

Building an online tool for spatial joins

How can you build your own tool ?


Install


• Apache I or II Webserver (or IIS)




PHP (php_Perl.dll, php_pgsql.dll etc.)




Perl (5.8 or higher, geocoder::US module and several others from CPAN)




PostgreSQL




PostGIS, load polygon data





Obtain US Census Tiger Line data for states in question




Load Tiger data into BerkeleyDB (utility comes with Geo::Coder::US)




author some PHP/Perl scripts

Technical Background



Building an online tool for spatial joins

Open source utilities and websites

Geocoder scripts


http://geocoder.us

Census Tiger Line
files

http://www.census.gov/geo/www/tiger/


PHP

http://www.php.net/downloads.php


Perl (Active State)


http://www.activestate.com/Products/ActivePerl

php_perl Extension

http://devzone.zend.com/node/view/id/1712


PostgreSQL

http://www.postgresql.org


PostGIS

http://postgis.refractions.net


BerkeleyDB

http://www.oracle.com/database/berkeley
-
db/index.html

Resources

Building an online tool for spatial joins

Articles


Build Your Own Geocoding Solution with Geo::Coder::US

By Jason Gilmore

http://www.developer.com/tech/article.php/3557171


Retrieving Map Location Coordinates

By Jason Gilmore

http://www.developer.com/tech/article.php/3548171

Resources


Building an online tool for spatial joins

Free and Open Source Software for Geospatial 2007


http://www.foss4g2007.org/

Resources


Building an online tool for spatial joins

New local GIS user group:


“Cascadia Users of Geospatial Open Source”


http://groups.google.com/group/cugos



Next meeting
today

April 25
th
, 5:30 pm

In the LizardTech offices, located near Pioneer Square:



The National Building

Suite 200

1008 Western Avenue

Seattle, WA 98104


Resources