# [postgis-users] Newbie Sanity Check: Zillow, US Census, Google Maps, QGis

David Jantzen david at culturemob.com
Tue Jun 17 11:25:21 PDT 2008

```Hi All, I'd like to describe what I'm doing and see if there are any
alarm bells going off in your minds.  I'm also struggling with some
concepts and maybe someone can elucidate them.

Immediate goals:
1) Using Quantum GIS, draw a polygon around the Seattle metro area.
2) Perform latitude/longitude queries to determine if the location (a
venue) is in the Seattle polygon.
3) Draw similar polygons around a handful of other metro areas.

Medium term goals:
2) Use the Zillow neighborhood data and perform lat/long queries to
determine the proper neighborhood.

Resources:
1) Zillow neighborhood boundaries:
http://www.zillow.com/labs/NeighborhoodBoundaries.htm
2) US Census urban areas boundaries:
http://nationalatlas.gov/atlasftp.html?openChapters=chpref#chpbound
4) Some guy's blog post on distance calculation :)
http://unserializableone.blogspot.com/2007/02/using-postgis-to-find-points-of.html

What *appears* to be working:

An "areas" table with name and geometry(POLYGON, 2 dim), SRID 4269
(geographic coordinate system NAD83).  Represents our notion of a metro
area.
A "us_census_urban_areas" table with the US Census data, SRID 4269.  The
role of this table is not for lookups, but to assist in drawing polygons
for our areas in QGis.
A "venues" table with a geometry(POINT) for lat/long pair, SRID 32661
(projected coordinate system WGS 84 / UPS North), populated by Google
Maps geocoding service.
﻿A "neighborhoods" table with the Zillow data, SRID 4269.

I'm able to draw a polygon around Seattle in QGis and write to the
"areas" table.  The following queries appear to work correctly:

# Is the Nectar in the Seattle metro area?
select within((select the_geom from venues where name = 'Nectar
Lounge'),transform((select the_geom from areas where name =
'seattle'),32661));

# Is the Nectar in Fremont?
select within((select the_geom from venues where name = 'Nectar
Lounge'),transform((select the_geom from neighborhoods where name =
'Fremont' and state = 'WA'),32661));

Questions:
1) Is my use of SRIDs correct?  Should I store venue lat/longs in 4269
also and transform them to 32661?
2) Are the lat/long geocodings supplied by Google Maps compatible with
my usage here?  Do they need to be transformed?
3) Are the minor discrepancies between the census boundaries and the
Zillow data due to a bad projection on my part or does the census data
just suck?
4) What's the difference between ST_Within and within?  They both appear
to call the same C function.
5-brain-fizzling) I'm trying to grok why transforms are necessary.  My
best understanding right now goes like this (please correct me!):  Data
sets like that from Zillow and the US Census come in the form of a
geographic coordinate system (datum, spheroid, and uh, other stuff).
This maps lat/long to geographical features.  It must be transformed via
a map projection in order to do calculations like distance, area,
containment, etc, because ... I don't know.  It seems like all those
calculations could be done using the speroid named in the GCS.  What am
I missing here?

Thanks very much!
David

```