[postgis-users] Re: postgis-users Digest, Vol 66, Issue 4
Dylan Lorimer
edylan at google.com
Mon Apr 14 13:41:21 PDT 2008
Many thanks to all who replied to my past 2 weeks ago. I ended up going with
Regina's suggested LEFT JOIN, which worked wonders. Question though:
Given Regin's suggested LEFT JOIN:
SELECT A.*
FROM A LEFT JOIN B ON ST_Intersects(A.the_geom, B.the_geom)
LEFT JOIN C ON ST_Intersects(A.the_geom, C.the_geom)
WHERE B.gid IS NULL AND C.gid IS NULL;
This finds me all geometries from A that have 0 intersection with B OR C,
which is what I was looking for. But what if I want to find all polys in A
that have <100% overlap with B or C. Meaning, some overlap is fine so long
as A is not contained by B or C.
Cheers,
dylan
On Fri, Apr 4, 2008 at 12:00 PM, <
postgis-users-request at postgis.refractions.net> wrote:
> Send postgis-users mailing list submissions to
> postgis-users at postgis.refractions.net
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> postgis-users-request at postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-owner at postgis.refractions.net
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
> 1. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 2. Invoking GIST index on 2 disjoints within a query (Dylan Lorimer)
> 3. Re: Invoking GIST index on 2 disjoints within a query
> (Paul Ramsey)
> 4. Re: TIGER/Line Shapefiles released (Jeshua Lacock)
> 5. Re: Invoking GIST index on 2 disjoints within a query
> (Kevin Neufeld)
> 6. RE: TIGER/Line Shapefiles released (Paragon Corporation)
> 7. TIGER/LINE Shapefiles SMID mapping (Paragon Corporation)
> 8. Re: Invoking GIST index on 2 disjoints within a query (nw)
> 9. RE: Invoking GIST index on 2 disjoints within a query
> (Paragon Corporation)
> 10. Re: TIGER/Line Shapefiles released (Jonathan W. Lowe)
> 11. Re: postgis (Colin Wetherbee)
> 12. Re: TIGER/Line Shapefiles released (Frank Durstewitz)
> 13. Re: TIGER/Line Shapefiles released (Jonathan W. Lowe)
> 14. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 15. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 16. RE: TIGER/Line Shapefiles released (Paragon Corporation)
> 17. Re: TIGER/Line Shapefiles released (Jonathan W. Lowe)
> 18. Re: TIGER/Line Shapefiles released (Ron M)
> 19. Re: TIGER/Line Shapefiles released (Jonathan W. Lowe)
> 20. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 21. Re: TIGER/Line Shapefiles released (Stephen Woodbridge)
> 22. Re: TIGER/Line Shapefiles released (Stephen Woodbridge)
> 23. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 24. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 25. RE: TIGER/Line Shapefiles released (Paragon Corporation)
> 26. Re: TIGER/Line Shapefiles released (Stephen Woodbridge)
> 27. Re: TIGER/Line Shapefiles released (Stephen Woodbridge)
> 28. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 29. Re: TIGER/Line Shapefiles released (Stephen Woodbridge)
> 30. Cannot display postGIS layers on ms4w 4.6.1
> (kreshna_iceheart at yahoo.com)
> 31. RE: Cannot display postGIS layers on ms4w 4.6.1
> (Paragon Corporation)
> 32. Re: Problem with the Codification/charset from Postgis to
> mapserver (Barend Kobben)
> 33. Re: TIGER/Line Shapefiles released (Jonathan W. Lowe)
> 34. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 35. Re: TIGER/Line Shapefiles released (Nick Black)
> 36. Re: TIGER/Line Shapefiles released (Stephen Frost)
> 37. Re: TIGER/Line Shapefiles released (Jonathan W. Lowe)
> 38. Re: TIGER/Line Shapefiles released (Nick Black)
> 39. ERROR: Operation on mixed SRID geometries after upgrading to
> recent postgres/postgis (Sebastian Reitenbach)
> 40. RE: ERROR: Operation on mixed SRID geometries afterupgrading
> to recent postgres/postgis (Obe, Regina)
> 41. Re: TIGER/Line Shapefiles released (Stephen Woodbridge)
> 42. RE: ERROR: Operation on mixed SRID geometries afterupgrading
> to recent postgres/postgis (Sebastian Reitenbach)
> 43. FOSS4G 2008 Call for Workshops / Papers (Paul Ramsey)
> 44. Loading Tiger2007fe data - Thoughts on using inherited tables
> (Stephen Woodbridge)
> 45. Re: Loading Tiger2007fe data - Thoughts on using inherited
> tables (Stephen Frost)
> 46. Re: Loading Tiger2007fe data - Thoughts on using inherited
> tables (Paul Ramsey)
> 47. Re: Loading Tiger2007fe data - Thoughts on using inherited
> tables (Stephen Frost)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 3 Apr 2008 17:07:46 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20080403210746.GV4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> * Stephen Frost (sfrost at snowman.net) wrote:
> > I think they may have also upgraded their pipe.. I got about 1.41MB/s
> > (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> > probably be trying to load it up into PG on one of our servers tomorrow.
> > It was a bit over 4 hours for me to pull down off of their
> > ftp2.census.gov ftp site.
>
> Just to update those who might be interested- I've finished the data
> load into one of our servers at work. It comes to ~60GB on disk in
> PostgreSQL/PostGIS with appropriate indexes in most places and whatnot.
> Based on what I've seen so far, it looks *very* nice, especially the
> hydrogrophy ("areawater"). It also appears to be pretty consistant
> across the layers, which is also good.
>
> If anyone's interested in the scripts used to load the data (they're
> pretty simple, really), I'd be happy to provide them.
>
> Enjoy,
>
> Stephen
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 189 bytes
> Desc: Digital signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/bb74af0c/attachment-0001.bin
>
> ------------------------------
>
> Message: 2
> Date: Thu, 3 Apr 2008 16:12:38 -0500
> From: "Dylan Lorimer" <edylan at google.com>
> Subject: [postgis-users] Invoking GIST index on 2 disjoints within a
> query
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <3ddac38d0804031412h16e7241fu2c3823a0e798edf6 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi,
>
> So I have probably a simple question but I don't use PostGIS enough to
> know the right way to go about this. Hoping someone can help.
>
> So I've got 3 spatial tables, each containing a bunch of polygon
> geometries. Let's call the tables A, B, C. What I want is to find out
> which polygons in table A are disjoint from all polygons in tables B
> and C. Meaning, which polygons in A have 0 overlap with polygons in B
> or C.
>
> I seem to have no troubles doing this when using only 2 tables, but
> adding the 3rd is messing things up. Oh, and I also want to invoke the
> GIST indexes to speed things up.
>
> Here's the (slightly generalized) query I think I should be using:
>
> SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,
> B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other
> attribute filters> AND A.the_geom && B.the_geom AND A.the_geom &&
> C.the_geom;
>
> I've not seen this query complete successfully as it takes so long,
> but I think something is wrong with it as running it against only A
> and B is really quick and there aren't too many geometries in the
> tables.
>
> Thoughts?
>
> Cheers,
> dylan
>
>
> ------------------------------
>
> Message: 3
> Date: Thu, 3 Apr 2008 14:23:27 -0700
> From: "Paul Ramsey" <pramsey at cleverelephant.ca>
> Subject: Re: [postgis-users] Invoking GIST index on 2 disjoints within
> a query
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <30fe546d0804031423k3169a38at3b02346530e3fe80 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Drop the &&. The indexes are not much help for testing Disjoint,
> unfortunately.
>
> The other thing you could do is re-cast it as a does-not-intersect
> test instead...
>
> SELECT <some stuff> FROM A, B, C WHERE NOT ( ST_Intersects(A.the_geom,
> B.the_geom) OR ST_Intersects(A.the_geom, C.the_geom) );
>
> (The above will be indexed automatically for recent versions of
> PostGIS. For older, add in the && clause manually like this.)
>
> SELECT <some stuff> FROM A, B, C WHERE NOT ( (A.the_geom && B.the_geom
> AND Intersects(A.the_geom, B.the_geom)) OR (A.the_geom && c.the_geom
> AND ST_Intersects(A.the_geom, C.the_geom) ) );
>
> YMMV... the B and C tables might be interacting and blowing up the
> query, I don't have an intuitive feel for this stuff like Regina :)
>
> P.
>
> On Thu, Apr 3, 2008 at 2:12 PM, Dylan Lorimer <edylan at google.com> wrote:
> > Hi,
> >
> > So I have probably a simple question but I don't use PostGIS enough to
> > know the right way to go about this. Hoping someone can help.
> >
> > So I've got 3 spatial tables, each containing a bunch of polygon
> > geometries. Let's call the tables A, B, C. What I want is to find out
> > which polygons in table A are disjoint from all polygons in tables B
> > and C. Meaning, which polygons in A have 0 overlap with polygons in B
> > or C.
> >
> > I seem to have no troubles doing this when using only 2 tables, but
> > adding the 3rd is messing things up. Oh, and I also want to invoke the
> > GIST indexes to speed things up.
> >
> > Here's the (slightly generalized) query I think I should be using:
> >
> > SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,
> > B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other
> > attribute filters> AND A.the_geom && B.the_geom AND A.the_geom &&
> > C.the_geom;
> >
> > I've not seen this query complete successfully as it takes so long,
> > but I think something is wrong with it as running it against only A
> > and B is really quick and there aren't too many geometries in the
> > tables.
> >
> > Thoughts?
> >
> > Cheers,
> > dylan
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 4
> Date: Thu, 3 Apr 2008 15:33:07 -0600
> From: Jeshua Lacock <jeshua at 3DTOPO.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>,
> Stephen Frost <sfrost at snowman.net>
> Message-ID: <C1CC27EC-9092-4FF3-B63E-4C22431211B7 at 3DTOPO.com>
> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
>
>
> On Apr 3, 2008, at 3:07 PM, Stephen Frost wrote:
>
> > If anyone's interested in the scripts used to load the data (they're
> > pretty simple, really), I'd be happy to provide them
>
> Hello Stephen,
>
> Sure; I'll bite. No sense reinventing the wheel if it can be helped.
> If it is more appropriate, please feel free to email me off list.
>
>
> Thanks,
>
> Jeshua Lacock
> Founder/Programmer
> 3DTOPO Incorporated
> <http://3DTOPO.com>
> Phone: 877.240.1364
>
>
>
> ------------------------------
>
> Message: 5
> Date: Thu, 03 Apr 2008 14:35:47 -0700
> From: Kevin Neufeld <kneufeld at refractions.net>
> Subject: Re: [postgis-users] Invoking GIST index on 2 disjoints within
> a query
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F54DB3.4070802 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Dylan,
>
> You could try something like this:
> SELECT <some stuff>
> FROM a
> LEFT JOIN b ON (ST_Intersects(a.the_geom, b.the_geom))
> LEFT JOIN c ON (ST_Intersects(a.the_geom, c.the_geom))
> WHERE b.the_geom IS NULL
> AND c.the_geom IS NULL;
>
> -- Kevin
>
> Dylan Lorimer wrote:
> > Hi,
> >
> > So I have probably a simple question but I don't use PostGIS enough to
> > know the right way to go about this. Hoping someone can help.
> >
> > So I've got 3 spatial tables, each containing a bunch of polygon
> > geometries. Let's call the tables A, B, C. What I want is to find out
> > which polygons in table A are disjoint from all polygons in tables B
> > and C. Meaning, which polygons in A have 0 overlap with polygons in B
> > or C.
> >
> > I seem to have no troubles doing this when using only 2 tables, but
> > adding the 3rd is messing things up. Oh, and I also want to invoke the
> > GIST indexes to speed things up.
> >
> > Here's the (slightly generalized) query I think I should be using:
> >
> > SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,
> > B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other
> > attribute filters> AND A.the_geom && B.the_geom AND A.the_geom &&
> > C.the_geom;
> >
> > I've not seen this query complete successfully as it takes so long,
> > but I think something is wrong with it as running it against only A
> > and B is really quick and there aren't too many geometries in the
> > tables.
> >
> > Thoughts?
> >
> > Cheers,
> > dylan
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 6
> Date: Thu, 3 Apr 2008 17:47:14 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: RE: [postgis-users] TIGER/Line Shapefiles released
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>, "'Stephen Frost'"
> <sfrost at snowman.net>
> Message-ID: <000e01c895d4$47c29900$4d812e40 at l>
> Content-Type: text/plain; charset="us-ascii"
>
> I would be interested too.
>
> Thanks,
> Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Jeshua
> Lacock
> Sent: Thursday, April 03, 2008 5:33 PM
> To: PostGIS Users Discussion; Stephen Frost
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
>
>
> On Apr 3, 2008, at 3:07 PM, Stephen Frost wrote:
>
> > If anyone's interested in the scripts used to load the data (they're
> > pretty simple, really), I'd be happy to provide them
>
> Hello Stephen,
>
> Sure; I'll bite. No sense reinventing the wheel if it can be helped.
> If it is more appropriate, please feel free to email me off list.
>
>
> Thanks,
>
> Jeshua Lacock
> Founder/Programmer
> 3DTOPO Incorporated
> <http://3DTOPO.com>
> Phone: 877.240.1364
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 7
> Date: Thu, 3 Apr 2008 17:51:47 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: [postgis-users] TIGER/LINE Shapefiles SMID mapping
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <001201c895d4$ea384220$4d812e40 at l>
> Content-Type: text/plain; charset="us-ascii"
>
> I've been looking at the TIGER/LINE 2007 shapefiles too and I notice that
> they have a field called SMID (Spatial Metadata Identifier) in the edges
> table. I thought these were supposed to be defined in the edges.shp.xml
> file or in the PDF help, but on closer inspection of that file I see this
>
> - <attr>
> <attrlabl>SMID</attrlabl>
> <attrdef>Spatial metadata identifier</attrdef>
> <attrdefs>U.S. Census Bureau</attrdefs>
> - <attrdomv>
> - <edom>
> <edomv>1</edomv>
> <edomvd>Spatial MID is positive, non-zero number</edomvd>
> <edomvds>U.S. Census Bureau</edomvds>
> </edom>
> </attrdomv>
> </attr>
>
>
> Well duh? I loaded all of Florida and there are 69 distinct values in that
> and I have no clue what they mean. Does any one by chance know where
> these
> codes are defined?
>
> Thanks,
> Regina
>
>
>
>
> ------------------------------
>
> Message: 8
> Date: Thu, 3 Apr 2008 17:01:43 -0500
> From: nw <nw at hydaspes.if.org>
> Subject: Re: [postgis-users] Invoking GIST index on 2 disjoints within
> a query
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <8913B9C0-76F3-467E-8809-EEDAD73A15A3 at hydaspes.if.org>
> Content-Type: text/plain; charset=US-ASCII; format=flowed
>
> On Apr 3, 2008, at 16:12, Dylan Lorimer wrote:
>
> > What I want is to find out which polygons in table A
> > are disjoint from all polygons in tables B and C.
>
> > Here's the (slightly generalized) query I think I should be using:
> >
> > SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,
> > B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other
> > attribute filters> AND A.the_geom && B.the_geom AND A.the_geom &&
> > C.the_geom;
> >
> > I've not seen this query complete successfully as it takes so long,
> > but I think something is wrong with it as running it against only A
> > and B is really quick and there aren't too many geometries in the
> > tables.
> >
> > Thoughts?
>
> Use a union.
>
> select <some stuff> from <working a vs b query>
> union
> select <some compatible stuff> from <working a vs c query>
> ;
>
> --
> nw
> nw at hydaspes.if.org
>
>
>
>
>
>
> ------------------------------
>
> Message: 9
> Date: Thu, 3 Apr 2008 18:14:37 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: RE: [postgis-users] Invoking GIST index on 2 disjoints within
> a query
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <000201c895d8$1b153b70$4d812e40 at l>
> Content-Type: text/plain; charset="us-ascii"
>
> If I understand you correctly, then I think the most efficient route is to
> figure out which ones overlap with an Intersect as Paul mentioned, and
> then
> throw them out. I presume you mean intersect rather than overlaps - ie.
> Overlaps means it can't be contained within where as intersects can be.
> Either way flip st_intersect with st_overlaps if you really want overlap
> check.
>
> SELECT A.*
> FROM A LEFT JOIN B ON ST_Intersects(A.the_geom, B.the_geom)
> LEFT JOIN C ON ST_Intersects(A.the_geom, C.the_geom)
>
> WHERE B.gid IS NULL AND C.gid IS NULL;
>
> Hope that helps,
> Regina
>
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
> Ramsey
> Sent: Thursday, April 03, 2008 5:23 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Invoking GIST index on 2 disjoints within a
> query
>
> Drop the &&. The indexes are not much help for testing Disjoint,
> unfortunately.
>
> The other thing you could do is re-cast it as a does-not-intersect test
> instead...
>
> SELECT <some stuff> FROM A, B, C WHERE NOT ( ST_Intersects(A.the_geom,
> B.the_geom) OR ST_Intersects(A.the_geom, C.the_geom) );
>
> (The above will be indexed automatically for recent versions of PostGIS.
> For
> older, add in the && clause manually like this.)
>
> SELECT <some stuff> FROM A, B, C WHERE NOT ( (A.the_geom && B.the_geom AND
> Intersects(A.the_geom, B.the_geom)) OR (A.the_geom && c.the_geom AND
> ST_Intersects(A.the_geom, C.the_geom) ) );
>
> YMMV... the B and C tables might be interacting and blowing up the query,
> I
> don't have an intuitive feel for this stuff like Regina :)
>
> P.
>
> On Thu, Apr 3, 2008 at 2:12 PM, Dylan Lorimer <edylan at google.com> wrote:
> > Hi,
> >
> > So I have probably a simple question but I don't use PostGIS enough
> > to know the right way to go about this. Hoping someone can help.
> >
> > So I've got 3 spatial tables, each containing a bunch of polygon
> > geometries. Let's call the tables A, B, C. What I want is to find out
> > which polygons in table A are disjoint from all polygons in tables B
> > and C. Meaning, which polygons in A have 0 overlap with polygons in B
> > or C.
> >
> > I seem to have no troubles doing this when using only 2 tables, but
> > adding the 3rd is messing things up. Oh, and I also want to invoke the
> > GIST indexes to speed things up.
> >
> > Here's the (slightly generalized) query I think I should be using:
> >
> > SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,
> > B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other
> > attribute filters> AND A.the_geom && B.the_geom AND A.the_geom &&
> > C.the_geom;
> >
> > I've not seen this query complete successfully as it takes so long,
> > but I think something is wrong with it as running it against only A
> > and B is really quick and there aren't too many geometries in the
> > tables.
> >
> > Thoughts?
> >
> > Cheers,
> > dylan
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 10
> Date: Thu, 03 Apr 2008 23:40:57 +0100
> From: "Jonathan W. Lowe" <jlowe at giswebsite.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <1207262457.4621.6.camel at localhost.localdomain>
> Content-Type: text/plain
>
> Stephen,
>
> Have you yet tried overlaying TIGER 2007 linework or census block/tract
> polygons over Google or OpenStreetMap tiles? I'm seeing a good match in
> some areas but a significant shift (~50 meters) in others. Thought it
> might be a datum conversion issue, but can't seem to find a match.
>
> Jonathan
>
> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> > * Stephen Frost (sfrost at snowman.net) wrote:
> > > I think they may have also upgraded their pipe.. I got about 1.41MB/s
> > > (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> > > probably be trying to load it up into PG on one of our servers
> tomorrow.
> > > It was a bit over 4 hours for me to pull down off of their
> > > ftp2.census.gov ftp site.
> >
> > Just to update those who might be interested- I've finished the data
> > load into one of our servers at work. It comes to ~60GB on disk in
> > PostgreSQL/PostGIS with appropriate indexes in most places and whatnot.
> > Based on what I've seen so far, it looks *very* nice, especially the
> > hydrogrophy ("areawater"). It also appears to be pretty consistant
> > across the layers, which is also good.
> >
> > If anyone's interested in the scripts used to load the data (they're
> > pretty simple, really), I'd be happy to provide them.
> >
> > Enjoy,
> >
> > Stephen
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 11
> Date: Thu, 03 Apr 2008 18:45:02 -0400
> From: Colin Wetherbee <cww at denterprises.org>
> Subject: Re: [postgis-users] postgis
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F55DEE.8050503 at denterprises.org>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> Gregory Williamson wrote:
> > It's been a while since I used MMS, but IIRC it does need a reference
> > to a unique id; OID is picked as a default if there is none
> > specified.
>
> Yes, this is true. It doesn't have to be any special or meaningful
> unique ID, like an OID or a primary key, but MapServer looks for those.
> Anything else must be explicitly specified.
>
> > I think you can put something in the DATA statement like the
> > following, assuming that the table has "gid" as a unique id (add a
> > serial column and populate it is an easy way to add such a thing).
> > Then:
> >
> > DATA "the_geom FROM (SELECT table1.the_geom AS the_geom, table1.gid
> > AS gid, table2.data AS data FROM table1 LEFT JOIN table2 ON table1.id
> > = table2.id) AS new_table USING UNIQUE gid USING SRID=-1"
> >
> > Which I think will make the interface use "gid" instead of trying for
> > OIDs, which are being deprecated in PostgreSQL itself.
>
> In general, that DATA statement looks correct.
>
> As another example, here's a DATA statement of mine.
>
> DATA "line FROM (
> SELECT num, id, line
> FROM jsview_journeyroutes
> WHERE userid = %userid%
> ) AS foo USING UNIQUE id USING SRID=4326"
>
> It's always a good idea to set the USING SRID=xxxx to the proper SRID
> for the data set.
>
> > You can also dump the table data, drop the table, recreate it with an
> > explicit "WITH OIDS" in the table creation, reload the data and away
> > you go without changing the MMS interface. But it's better to get rid
> > of OIDs where you can since they are not reliablely unique.
>
> Yes, it's better to get rid of OIDs. Creating an UNIQUE SERIAL column
> is preferable.
>
> Colin
>
>
> ------------------------------
>
> Message: 12
> Date: Fri, 04 Apr 2008 00:58:45 +0200
> From: Frank Durstewitz <frank.durstewitz at emporis.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F56125.9050308 at emporis.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Skipped content of type multipart/alternative-------------- next part
> --------------
> A non-text attachment was scrubbed...
> Name: smime.p7s
> Type: application/x-pkcs7-signature
> Size: 3281 bytes
> Desc: S/MIME Cryptographic Signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/d7c9cbe9/smime-0001.bin
>
> ------------------------------
>
> Message: 13
> Date: Fri, 04 Apr 2008 00:09:54 +0100
> From: "Jonathan W. Lowe" <jlowe at giswebsite.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <1207264194.4621.8.camel at localhost.localdomain>
> Content-Type: text/plain
>
> I'm interested as well.
> Thanks, Jonathan
>
> On Fri, 2008-04-04 at 00:58 +0200, Frank Durstewitz wrote:
> > Me too, please.
> >
> > Regards, Frank
> >
> > Paragon Corporation am 03.04.2008 23:47:
> > > I would be interested too.
> > >
> > > Thanks,
> > > Regina
> > >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net
> > > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Jeshua
> > > Lacock
> > > Sent: Thursday, April 03, 2008 5:33 PM
> > > To: PostGIS Users Discussion; Stephen Frost
> > > Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> > >
> > >
> > > On Apr 3, 2008, at 3:07 PM, Stephen Frost wrote:
> > >
> > >
> > > > If anyone's interested in the scripts used to load the data (they're
> > > > pretty simple, really), I'd be happy to provide them
> > > >
> > >
> > > Hello Stephen,
> > >
> > > Sure; I'll bite. No sense reinventing the wheel if it can be helped.
> > > If it is more appropriate, please feel free to email me off list.
> > >
> > >
> > > Thanks,
> > >
> > > Jeshua Lacock
> > > Founder/Programmer
> > > 3DTOPO Incorporated
> > > <http://3DTOPO.com>
> > > Phone: 877.240.1364
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 14
> Date: Thu, 3 Apr 2008 19:15:54 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20080403231553.GW4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> * Stephen Frost (sfrost at snowman.net) wrote:
> > If anyone's interested in the scripts used to load the data (they're
> > pretty simple, really), I'd be happy to provide them.
>
> Alright, since it's apparently kind of popular, I went ahead and put the
> script up here:
>
> http://snowman.net/tiger/
>
> I also included a README.txt that is essentially what I wrote up to the
> first person who asked me for the script. Please let me know if you
> improve upon it or find problems with it.
>
> Thanks,
>
> Stephen
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 189 bytes
> Desc: Digital signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/3bc528c7/attachment-0001.bin
>
> ------------------------------
>
> Message: 15
> Date: Thu, 3 Apr 2008 19:18:00 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: "Jonathan W. Lowe" <jlowe at giswebsite.com>, PostGIS Users
> Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20080403231800.GX4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Jonathan,
>
> * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> > Have you yet tried overlaying TIGER 2007 linework or census block/tract
> > polygons over Google or OpenStreetMap tiles? I'm seeing a good match in
> > some areas but a significant shift (~50 meters) in others. Thought it
> > might be a datum conversion issue, but can't seem to find a match.
>
> I hadn't looked at the linework too much yet or tried to overlay it.
> I'm curious where you're seeing the differences though because I know
> that Census is only about half way through their MAF improvment project
> and I actually have some info about what has been done so far and what
> hasn't. It'd be interesting to see if it matches up.
>
> There are a few places (Guam, Hawaii islands) where they actually do use
> an SRID other than 4269, but my scripts don't yet handle that and I'm
> guessing that's not what you're referring to anyway. :)
>
> Thanks!
>
> Stephen
>
> > On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> > > * Stephen Frost (sfrost at snowman.net) wrote:
> > > > I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> > > > (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> > > > probably be trying to load it up into PG on one of our servers
> tomorrow.
> > > > It was a bit over 4 hours for me to pull down off of their
> > > > ftp2.census.gov ftp site.
> > >
> > > Just to update those who might be interested- I've finished the data
> > > load into one of our servers at work. It comes to ~60GB on disk in
> > > PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> > > Based on what I've seen so far, it looks *very* nice, especially the
> > > hydrogrophy ("areawater"). It also appears to be pretty consistant
> > > across the layers, which is also good.
> > >
> > > If anyone's interested in the scripts used to load the data (they're
> > > pretty simple, really), I'd be happy to provide them.
> > >
> > > Enjoy,
> > >
> > > Stephen
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 189 bytes
> Desc: Digital signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/d5bf23c9/attachment-0001.bin
>
> ------------------------------
>
> Message: 16
> Date: Thu, 3 Apr 2008 19:37:36 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: RE: [postgis-users] TIGER/Line Shapefiles released
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <006601c895e3$b26a87e0$4d812e40 at l>
> Content-Type: text/plain; charset="us-ascii"
>
> As a side note to what you were saying in the .sh file it would be really
> nice if the shp2pgsql dealt with standalone dbf files too. I mean the
> logic
> is all there so doesn't seem like it would be that hard to put in a switch
> for that.
>
> For the dbfs I was using Ogr2Ogr which works well except it adds a useless
> geometry field.
>
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Stephen
> Frost
> Sent: Thursday, April 03, 2008 7:16 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
>
> * Stephen Frost (sfrost at snowman.net) wrote:
> > If anyone's interested in the scripts used to load the data (they're
> > pretty simple, really), I'd be happy to provide them.
>
> Alright, since it's apparently kind of popular, I went ahead and put the
> script up here:
>
> http://snowman.net/tiger/
>
> I also included a README.txt that is essentially what I wrote up to the
> first person who asked me for the script. Please let me know if you
> improve
> upon it or find problems with it.
>
> Thanks,
>
> Stephen
>
>
>
>
> ------------------------------
>
> Message: 17
> Date: Fri, 04 Apr 2008 01:07:03 +0100
> From: "Jonathan W. Lowe" <jlowe at giswebsite.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: Stephen Frost <sfrost at snowman.net>
> Cc: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <1207267623.4932.38.camel at localhost.localdomain>
> Content-Type: text/plain; charset="us-ascii"
>
> Stephen,
>
> My initial testing has been on Alameda County (California) TIGER data.
> The two attached image files show an overlay of US Census 2000 Blocks
> over an area south of the UC Berkeley campus. The offset is the same
> for both Google and OpenStreetMap (OSM). This suggests that I've made a
> mistake somewhere, because the OSM tiles in the United States are all
> rendered from TIGER linework, so the TIGER census blocks should match
> exactly.
>
> For the same source shapefile (tabblock00.shp), there's a nearly perfect
> match between block boundaries and streets in the area just South of
> Oakland's Lake Merritt. It smells like a datum conversion issue...
>
> The conversion path was from shapefile to PostGIS using shp2pgsql. I
> used a custom projection of 32767 rather than 4269 because the existing
> srtext for 4269 had a degree value as 0.01745329251994328, but the US
> Census metadata listed a degree value of 0.017453292519943295. Perhaps
> not significant? My spatial_ref_sys entries for 4269 and 32767 are
> otherwise pretty similar:
>
> SRID: 4269
> SRTEXT: GEOGCS["NAD83",DATUM["North_American_Datum_1983",
> SPHEROID["GRS 1980",6378137,298.257222101,
> AUTHORITY["EPSG","7019"]],
> AUTHORITY["EPSG","6269"]],
> PRIMEM["Greenwich",0,
> AUTHORITY["EPSG","8901"]],
> UNIT["degree",0.01745329251994328,
> AUTHORITY["EPSG","9122"]],
> AUTHORITY["EPSG","4269"]]
> PROJ4TEXT: +proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs
>
> SRID: 32767
> SRTEXT: GEOGCS["GCS_North_American_1983",
> DATUM["D_North_American_1983",
> SPHEROID["GRS_1980",6378137,298.257222101]],
> PRIMEM["Greenwich",0],
> UNIT["Degree",0.017453292519943295]]
> PROJ4TEXT: +proj=longlat +ellps=clrk66 +datum=NAD27 +no_defs
>
> To display census block data in OpenStreetMap, I extract it from PostGIS
> with a transform to EPSG 4326, although the coordinates don't seem to
> change as a result. (This seems correct, as datum=NAD83 and datum=WGS84
> are, for my purposes at least, are essentially identical.)
>
> Thanks,
> Jonathan
>
> 2 attachments: TIGER2007andOSM.png, TIGER2007andGoogle.png
>
>
> On Thu, 2008-04-03 at 19:18 -0400, Stephen Frost wrote:
> > Jonathan,
> >
> > * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> > > Have you yet tried overlaying TIGER 2007 linework or census
> block/tract
> > > polygons over Google or OpenStreetMap tiles? I'm seeing a good match
> in
> > > some areas but a significant shift (~50 meters) in others. Thought it
> > > might be a datum conversion issue, but can't seem to find a match.
> >
> > I hadn't looked at the linework too much yet or tried to overlay it.
> > I'm curious where you're seeing the differences though because I know
> > that Census is only about half way through their MAF improvment project
> > and I actually have some info about what has been done so far and what
> > hasn't. It'd be interesting to see if it matches up.
> >
> > There are a few places (Guam, Hawaii islands) where they actually do use
> > an SRID other than 4269, but my scripts don't yet handle that and I'm
> > guessing that's not what you're referring to anyway. :)
> >
> > Thanks!
> >
> > Stephen
> >
> > > On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> > > > * Stephen Frost (sfrost at snowman.net) wrote:
> > > > > I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> > > > > (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> > > > > probably be trying to load it up into PG on one of our servers
> tomorrow.
> > > > > It was a bit over 4 hours for me to pull down off of their
> > > > > ftp2.census.gov ftp site.
> > > >
> > > > Just to update those who might be interested- I've finished the data
> > > > load into one of our servers at work. It comes to ~60GB on disk in
> > > > PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> > > > Based on what I've seen so far, it looks *very* nice, especially the
> > > > hydrogrophy ("areawater"). It also appears to be pretty consistant
> > > > across the layers, which is also good.
> > > >
> > > > If anyone's interested in the scripts used to load the data (they're
> > > > pretty simple, really), I'd be happy to provide them.
> > > >
> > > > Enjoy,
> > > >
> > > > Stephen
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: TIGER2007andGoogle.png
> Type: image/png
> Size: 123343 bytes
> Desc: not available
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/5c180b3f/TIGER2007andGoogle-0001.png
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: TIGER2007andOSM.png
> Type: image/png
> Size: 252886 bytes
> Desc: not available
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/5c180b3f/TIGER2007andOSM-0001.png
>
> ------------------------------
>
> Message: 18
> Date: Thu, 03 Apr 2008 17:08:29 -0700
> From: Ron M <rm_postgis at cheapcomplexdevices.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: "Jonathan W. Lowe" <jlowe at giswebsite.com>, PostGIS Users
> Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F5717D.5080301 at cheapcomplexdevices.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>
> I'm curious if they've fixed some of the self-inconsistencies they
> seem to have had (or did I just load the data wrong) in the 2006 (or
> was it 2005) data I loaded earlier.
>
> An example is Interstate 280's odd gap behind Stanford.
>
>
> http://map1.forensiclogic.com/maps/mapcache.pl?userid=1&sessionid=0&features=0&layer=land&layer=roads&layer=userfeatures&map_size=640+480&map=cp.map&mapext=-122.2233+37.38634+-122.1655+37.42966&mode=map
>
> Other examples were the Dumbarton and San Mateo bridges across
> San Francisco Bay not quite connecting with each other; but I
> can't provide a link for that because IIRC we hand-edited those.
>
>
> Stephen Frost wrote:
> > Jonathan,
> >
> > * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> >> Have you yet tried overlaying TIGER 2007 linework or census block/tract
> >> polygons over Google or OpenStreetMap tiles? I'm seeing a good match
> in
> >> some areas but a significant shift (~50 meters) in others. Thought it
> >> might be a datum conversion issue, but can't seem to find a match.
> >
> > I hadn't looked at the linework too much yet or tried to overlay it.
> > I'm curious where you're seeing the differences though because I know
> > that Census is only about half way through their MAF improvment project
> > and I actually have some info about what has been done so far and what
> > hasn't. It'd be interesting to see if it matches up.
> >
> > There are a few places (Guam, Hawaii islands) where they actually do use
> > an SRID other than 4269, but my scripts don't yet handle that and I'm
> > guessing that's not what you're referring to anyway. :)
> >
> > Thanks!
> >
> > Stephen
> >
> >> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> >>> * Stephen Frost (sfrost at snowman.net) wrote:
> >>>> I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> >>>> (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> >>>> probably be trying to load it up into PG on one of our servers
> tomorrow.
> >>>> It was a bit over 4 hours for me to pull down off of their
> >>>> ftp2.census.gov ftp site.
> >>> Just to update those who might be interested- I've finished the data
> >>> load into one of our servers at work. It comes to ~60GB on disk in
> >>> PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> >>> Based on what I've seen so far, it looks *very* nice, especially the
> >>> hydrogrophy ("areawater"). It also appears to be pretty consistant
> >>> across the layers, which is also good.
> >>>
> >>> If anyone's interested in the scripts used to load the data (they're
> >>> pretty simple, really), I'd be happy to provide them.
> >>>
> >>> Enjoy,
> >>>
> >>> Stephen
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>
> ------------------------------------------------------------------------
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 19
> Date: Fri, 04 Apr 2008 01:27:48 +0100
> From: "Jonathan W. Lowe" <jlowe at giswebsite.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <1207268868.4932.43.camel at localhost.localdomain>
> Content-Type: text/plain
>
> ...And in case the images don't persist through the mail server, they're
> viewable at: http://www.giswebsite.com/demos/tiger_overlays.html
>
> On Fri, 2008-04-04 at 01:07 +0100, Jonathan W. Lowe wrote:
> > Stephen,
> >
> > My initial testing has been on Alameda County (California) TIGER data.
> > The two attached image files show an overlay of US Census 2000 Blocks
> > over an area south of the UC Berkeley campus. The offset is the same
> > for both Google and OpenStreetMap (OSM). This suggests that I've made a
> > mistake somewhere, because the OSM tiles in the United States are all
> > rendered from TIGER linework, so the TIGER census blocks should match
> > exactly.
> >
> > For the same source shapefile (tabblock00.shp), there's a nearly perfect
> > match between block boundaries and streets in the area just South of
> > Oakland's Lake Merritt. It smells like a datum conversion issue...
> >
> > The conversion path was from shapefile to PostGIS using shp2pgsql. I
> > used a custom projection of 32767 rather than 4269 because the existing
> > srtext for 4269 had a degree value as 0.01745329251994328, but the US
> > Census metadata listed a degree value of 0.017453292519943295. Perhaps
> > not significant? My spatial_ref_sys entries for 4269 and 32767 are
> > otherwise pretty similar:
> >
> > SRID: 4269
> > SRTEXT: GEOGCS["NAD83",DATUM["North_American_Datum_1983",
> > SPHEROID["GRS 1980",6378137,298.257222101,
> > AUTHORITY["EPSG","7019"]],
> > AUTHORITY["EPSG","6269"]],
> > PRIMEM["Greenwich",0,
> > AUTHORITY["EPSG","8901"]],
> > UNIT["degree",0.01745329251994328,
> > AUTHORITY["EPSG","9122"]],
> > AUTHORITY["EPSG","4269"]]
> > PROJ4TEXT: +proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs
> >
> > SRID: 32767
> > SRTEXT: GEOGCS["GCS_North_American_1983",
> > DATUM["D_North_American_1983",
> > SPHEROID["GRS_1980",6378137,298.257222101]],
> > PRIMEM["Greenwich",0],
> > UNIT["Degree",0.017453292519943295]]
> > PROJ4TEXT: +proj=longlat +ellps=clrk66 +datum=NAD27 +no_defs
> >
> > To display census block data in OpenStreetMap, I extract it from PostGIS
> > with a transform to EPSG 4326, although the coordinates don't seem to
> > change as a result. (This seems correct, as datum=NAD83 and datum=WGS84
> > are, for my purposes at least, are essentially identical.)
> >
> > Thanks,
> > Jonathan
> >
> > 2 attachments: TIGER2007andOSM.png, TIGER2007andGoogle.png
> >
> >
> > On Thu, 2008-04-03 at 19:18 -0400, Stephen Frost wrote:
> > > Jonathan,
> > >
> > > * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> > > > Have you yet tried overlaying TIGER 2007 linework or census
> block/tract
> > > > polygons over Google or OpenStreetMap tiles? I'm seeing a good
> match in
> > > > some areas but a significant shift (~50 meters) in others. Thought
> it
> > > > might be a datum conversion issue, but can't seem to find a match.
> > >
> > > I hadn't looked at the linework too much yet or tried to overlay it.
> > > I'm curious where you're seeing the differences though because I know
> > > that Census is only about half way through their MAF improvment
> project
> > > and I actually have some info about what has been done so far and what
> > > hasn't. It'd be interesting to see if it matches up.
> > >
> > > There are a few places (Guam, Hawaii islands) where they actually do
> use
> > > an SRID other than 4269, but my scripts don't yet handle that and I'm
> > > guessing that's not what you're referring to anyway. :)
> > >
> > > Thanks!
> > >
> > > Stephen
> > >
> > > > On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> > > > > * Stephen Frost (sfrost at snowman.net) wrote:
> > > > > > I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> > > > > > (11 Mb/s) for the whole transfer. It's about 22G all told.
> I'll
> > > > > > probably be trying to load it up into PG on one of our servers
> tomorrow.
> > > > > > It was a bit over 4 hours for me to pull down off of their
> > > > > > ftp2.census.gov ftp site.
> > > > >
> > > > > Just to update those who might be interested- I've finished the
> data
> > > > > load into one of our servers at work. It comes to ~60GB on disk
> in
> > > > > PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> > > > > Based on what I've seen so far, it looks *very* nice, especially
> the
> > > > > hydrogrophy ("areawater"). It also appears to be pretty
> consistant
> > > > > across the layers, which is also good.
> > > > >
> > > > > If anyone's interested in the scripts used to load the data
> (they're
> > > > > pretty simple, really), I'd be happy to provide them.
> > > > >
> > > > > Enjoy,
> > > > >
> > > > > Stephen
> > > > > _______________________________________________
> > > > > postgis-users mailing list
> > > > > postgis-users at postgis.refractions.net
> > > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > >
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 20
> Date: Thu, 3 Apr 2008 20:31:32 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: "Jonathan W. Lowe" <jlowe at giswebsite.com>, PostGIS Users
> Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20080404003132.GA4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Jonathan,
>
> * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> > ...And in case the images don't persist through the mail server, they're
> > viewable at: http://www.giswebsite.com/demos/tiger_overlays.html
>
> You know, I just realized that you were talking about the Census 2000
> blocks (tabblock00.shp). Is there some reason you're using that
> instead of the current data (tabblock.shp)? They might not want to
> update the data from 2000 for historical reasons...
> (Note: I havn't actually gone and looked, it just occured to me..)
>
> Thanks,
>
> Stephen
>
> > On Fri, 2008-04-04 at 01:07 +0100, Jonathan W. Lowe wrote:
> > > Stephen,
> > >
> > > My initial testing has been on Alameda County (California) TIGER data.
> > > The two attached image files show an overlay of US Census 2000 Blocks
> > > over an area south of the UC Berkeley campus. The offset is the same
> > > for both Google and OpenStreetMap (OSM). This suggests that I've made
> a
> > > mistake somewhere, because the OSM tiles in the United States are all
> > > rendered from TIGER linework, so the TIGER census blocks should match
> > > exactly.
> > >
> > > For the same source shapefile (tabblock00.shp), there's a nearly
> perfect
> > > match between block boundaries and streets in the area just South of
> > > Oakland's Lake Merritt. It smells like a datum conversion issue...
> > >
> > > The conversion path was from shapefile to PostGIS using shp2pgsql. I
> > > used a custom projection of 32767 rather than 4269 because the
> existing
> > > srtext for 4269 had a degree value as 0.01745329251994328, but the US
> > > Census metadata listed a degree value of 0.017453292519943295.
> Perhaps
> > > not significant? My spatial_ref_sys entries for 4269 and 32767 are
> > > otherwise pretty similar:
> > >
> > > SRID: 4269
> > > SRTEXT: GEOGCS["NAD83",DATUM["North_American_Datum_1983",
> > > SPHEROID["GRS 1980",6378137,298.257222101,
> > > AUTHORITY["EPSG","7019"]],
> > > AUTHORITY["EPSG","6269"]],
> > > PRIMEM["Greenwich",0,
> > > AUTHORITY["EPSG","8901"]],
> > > UNIT["degree",0.01745329251994328,
> > > AUTHORITY["EPSG","9122"]],
> > > AUTHORITY["EPSG","4269"]]
> > > PROJ4TEXT: +proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs
> > >
> > > SRID: 32767
> > > SRTEXT: GEOGCS["GCS_North_American_1983",
> > > DATUM["D_North_American_1983",
> > > SPHEROID["GRS_1980",6378137,298.257222101]],
> > > PRIMEM["Greenwich",0],
> > > UNIT["Degree",0.017453292519943295]]
> > > PROJ4TEXT: +proj=longlat +ellps=clrk66 +datum=NAD27 +no_defs
> > >
> > > To display census block data in OpenStreetMap, I extract it from
> PostGIS
> > > with a transform to EPSG 4326, although the coordinates don't seem to
> > > change as a result. (This seems correct, as datum=NAD83 and
> datum=WGS84
> > > are, for my purposes at least, are essentially identical.)
> > >
> > > Thanks,
> > > Jonathan
> > >
> > > 2 attachments: TIGER2007andOSM.png, TIGER2007andGoogle.png
> > >
> > >
> > > On Thu, 2008-04-03 at 19:18 -0400, Stephen Frost wrote:
> > > > Jonathan,
> > > >
> > > > * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> > > > > Have you yet tried overlaying TIGER 2007 linework or census
> block/tract
> > > > > polygons over Google or OpenStreetMap tiles? I'm seeing a good
> match in
> > > > > some areas but a significant shift (~50 meters) in others.
> Thought it
> > > > > might be a datum conversion issue, but can't seem to find a match.
> > > >
> > > > I hadn't looked at the linework too much yet or tried to overlay it.
> > > > I'm curious where you're seeing the differences though because I
> know
> > > > that Census is only about half way through their MAF improvment
> project
> > > > and I actually have some info about what has been done so far and
> what
> > > > hasn't. It'd be interesting to see if it matches up.
> > > >
> > > > There are a few places (Guam, Hawaii islands) where they actually do
> use
> > > > an SRID other than 4269, but my scripts don't yet handle that and
> I'm
> > > > guessing that's not what you're referring to anyway. :)
> > > >
> > > > Thanks!
> > > >
> > > > Stephen
> > > >
> > > > > On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> > > > > > * Stephen Frost (sfrost at snowman.net) wrote:
> > > > > > > I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> > > > > > > (11 Mb/s) for the whole transfer. It's about 22G all told.
> I'll
> > > > > > > probably be trying to load it up into PG on one of our servers
> tomorrow.
> > > > > > > It was a bit over 4 hours for me to pull down off of their
> > > > > > > ftp2.census.gov ftp site.
> > > > > >
> > > > > > Just to update those who might be interested- I've finished the
> data
> > > > > > load into one of our servers at work. It comes to ~60GB on disk
> in
> > > > > > PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> > > > > > Based on what I've seen so far, it looks *very* nice, especially
> the
> > > > > > hydrogrophy ("areawater"). It also appears to be pretty
> consistant
> > > > > > across the layers, which is also good.
> > > > > >
> > > > > > If anyone's interested in the scripts used to load the data
> (they're
> > > > > > pretty simple, really), I'd be happy to provide them.
> > > > > >
> > > > > > Enjoy,
> > > > > >
> > > > > > Stephen
> > > > > > _______________________________________________
> > > > > > postgis-users mailing list
> > > > > > postgis-users at postgis.refractions.net
> > > > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > > >
> > > > > _______________________________________________
> > > > > postgis-users mailing list
> > > > > postgis-users at postgis.refractions.net
> > > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 189 bytes
> Desc: Digital signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/dac89b9a/attachment-0001.bin
>
> ------------------------------
>
> Message: 21
> Date: Thu, 03 Apr 2008 20:48:49 -0500
> From: Stephen Woodbridge <woodbri at swoodbridge.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: "Jonathan W. Lowe" <jlowe at giswebsite.com>, PostGIS Users
> Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F58901.3050701 at swoodbridge.com>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> Stephen Frost wrote:
> > Jonathan,
> >
> > * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> >> Have you yet tried overlaying TIGER 2007 linework or census block/tract
> >> polygons over Google or OpenStreetMap tiles? I'm seeing a good match
> in
> >> some areas but a significant shift (~50 meters) in others. Thought it
> >> might be a datum conversion issue, but can't seem to find a match.
> >
> > I hadn't looked at the linework too much yet or tried to overlay it.
> > I'm curious where you're seeing the differences though because I know
> > that Census is only about half way through their MAF improvment project
> > and I actually have some info about what has been done so far and what
> > hasn't. It'd be interesting to see if it matches up.
> >
> > There are a few places (Guam, Hawaii islands) where they actually do use
> > an SRID other than 4269, but my scripts don't yet handle that and I'm
> > guessing that's not what you're referring to anyway. :)
>
> I had an extensive discussion with some of the Census Geography staff
> about the Island provinces and Hawaii because I was trying to align
> Navteq routes over Tiger data and the Navteq routes were about a .25-.5
> miles east of the Tiger data in Hawaii.
>
> It turns out that they really have not idea what the "local datums" are
> that were originally used. I tried a lot of the local Island datums in
> the proj4 epsg file but could not find any close matches.
>
> It will be nice when they get that fixed up :)
>
> Stephen - thank you for sharing your scripts.
>
> Best regards,
> -Stephen Woodbridge
> http://imaptools.com/
>
> > Thanks!
> >
> > Stephen
> >
> >> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> >>> * Stephen Frost (sfrost at snowman.net) wrote:
> >>>> I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> >>>> (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> >>>> probably be trying to load it up into PG on one of our servers
> tomorrow.
> >>>> It was a bit over 4 hours for me to pull down off of their
> >>>> ftp2.census.gov ftp site.
> >>> Just to update those who might be interested- I've finished the data
> >>> load into one of our servers at work. It comes to ~60GB on disk in
> >>> PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> >>> Based on what I've seen so far, it looks *very* nice, especially the
> >>> hydrogrophy ("areawater"). It also appears to be pretty consistant
> >>> across the layers, which is also good.
> >>>
> >>> If anyone's interested in the scripts used to load the data (they're
> >>> pretty simple, really), I'd be happy to provide them.
> >>>
> >>> Enjoy,
> >>>
> >>> Stephen
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>
> ------------------------------------------------------------------------
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 22
> Date: Thu, 03 Apr 2008 20:52:50 -0500
> From: Stephen Woodbridge <woodbri at swoodbridge.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F589F2.4060804 at swoodbridge.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Yes, it would be ideal if it was handled by shp2pgsql but have you
> looked at:
>
>
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search
>
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search
>
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search
>
> some of these might be helpful.
>
> Best regards,
> -Stephen Woodbridge
> http://imaptools.com/
>
> Paragon Corporation wrote:
> > As a side note to what you were saying in the .sh file it would be
> really
> > nice if the shp2pgsql dealt with standalone dbf files too. I mean the
> logic
> > is all there so doesn't seem like it would be that hard to put in a
> switch
> > for that.
> >
> > For the dbfs I was using Ogr2Ogr which works well except it adds a
> useless
> > geometry field.
> >
> >
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Stephen
> > Frost
> > Sent: Thursday, April 03, 2008 7:16 PM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> >
> > * Stephen Frost (sfrost at snowman.net) wrote:
> >> If anyone's interested in the scripts used to load the data (they're
> >> pretty simple, really), I'd be happy to provide them.
> >
> > Alright, since it's apparently kind of popular, I went ahead and put the
> > script up here:
> >
> > http://snowman.net/tiger/
> >
> > I also included a README.txt that is essentially what I wrote up to the
> > first person who asked me for the script. Please let me know if you
> improve
> > upon it or find problems with it.
> >
> > Thanks,
> >
> > Stephen
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 23
> Date: Thu, 3 Apr 2008 20:59:43 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20080404005943.GB4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Stephen,
>
> * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> > I had an extensive discussion with some of the Census Geography staff
> > about the Island provinces and Hawaii because I was trying to align
> > Navteq routes over Tiger data and the Navteq routes were about a .25-.5
> > miles east of the Tiger data in Hawaii.
>
> Interesting...
>
> > It turns out that they really have not idea what the "local datums" are
> > that were originally used. I tried a lot of the local Island datums in
> > the proj4 epsg file but could not find any close matches.
>
> In their documentation they talk about the datums used for these
> regions.. Lemme go look up what they say..
>
> Here we go:
>
> North American Datum of 1983 in the 48 contiguous states, the District
> of Columbia, Alaska, Hawaii (only Oahu Island within Honolulu County, HI
> (15003)), Puerto Rico, and the U.S. Virgin Islands.
>
> Regional datums are used in the Pacific Island Areas (American Samoa,
> Guam, and the Commonwealth of the Northern Mariana Islands) and most of
> Hawaii.
>
> The datums used in the remainder of Hawaii are as follows: Hawaii
> County, HI (15001), Old Hawaiian Datum; Honolulu County, HI (15003),
> local astronomic datums for all islands northwest of 161 degrees west
> longitude; Kalawao County, HI (15005), Old Hawaiian Datum; Kauai County,
> HI (15007), Old Hawaiian Datum for Kauai Island and local astronomic
> datum for Kaula Rock; Maui County, HI (15009), Old Hawaiian Datum.
>
> The datums used in American Samoa are as follows: Eastern District, AS
> (60010), American Samoa Datum of 1962; Manu'a District, AS (60020),
> American Samoa Datum of 1962; Rose Island, AS (60030), local astronomic
> datum; Swains Island, AS (60040), local astronomic datum 1939; Western
> District, AS (60050), American Samoa Datum of 1962.
>
> The Guam Datum of 1963 is used in Guam (66010). The datums used in the
> Commonwealth of the Northern Mariana Islands are as follows: Northern
> Islands Municipality (69085), Guam Datum of 1963 (Agrihan, Alamagan,
> Anatahan, Gugan, Medinilla, Pagan, and Sarigan Islands) and local
> astronomic datums (Asuncion, Maug, and Farallon De Pajaros Islands);
> Rota Municipality (69100), Guam Datum of 1963; Saipan Municipality
> (69110), Guam Datum of 1963; Tinian Municipality (69120), Guam Datum of
> 1963.
>
> So, there's somewhat more information there than perhaps there was
> previously, but I havn't gone hunting for, eg, "Old Hawaiian Datum". If
> someone does come up with the correct datums/srids/etc based off of this
> (or whatever), I'd be happy to update my script accordingly.
>
> Thanks!
>
> Stephen
>
> > It will be nice when they get that fixed up :)
> >
> > Stephen - thank you for sharing your scripts.
> >
> > Best regards,
> > -Stephen Woodbridge
> > http://imaptools.com/
> >
> >> Thanks!
> >>
> >> Stephen
> >>
> >>> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> >>>> * Stephen Frost (sfrost at snowman.net) wrote:
> >>>>> I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> >>>>> (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> >>>>> probably be trying to load it up into PG on one of our servers
> tomorrow.
> >>>>> It was a bit over 4 hours for me to pull down off of their
> >>>>> ftp2.census.gov ftp site.
> >>>> Just to update those who might be interested- I've finished the data
> >>>> load into one of our servers at work. It comes to ~60GB on disk in
> >>>> PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> >>>> Based on what I've seen so far, it looks *very* nice, especially the
> >>>> hydrogrophy ("areawater"). It also appears to be pretty consistant
> >>>> across the layers, which is also good.
> >>>>
> >>>> If anyone's interested in the scripts used to load the data (they're
> >>>> pretty simple, really), I'd be happy to provide them.
> >>>>
> >>>> Enjoy,
> >>>>
> >>>> Stephen
> >>>> _______________________________________________
> >>>> postgis-users mailing list
> >>>> postgis-users at postgis.refractions.net
> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>>
> ------------------------------------------------------------------------
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 189 bytes
> Desc: Digital signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/cd560eac/attachment-0001.bin
>
> ------------------------------
>
> Message: 24
> Date: Thu, 3 Apr 2008 21:01:09 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20080404010108.GC4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> > Yes, it would be ideal if it was handled by shp2pgsql but have you
> > looked at:
> >
> >
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search
> >
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search
> >
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search
> >
> > some of these might be helpful.
>
> Just fyi, my script uses dbview from the, conveniantly named, dbview
> Debian package. It works quite well for me. Not that I'd be against
> having that functionality in shp2pgsql, it's certainly be useful.
>
> First I'd like to see an option to add the primary key *after* the data
> is loaded though.
>
> Thanks,
>
> Stephen
>
> > Paragon Corporation wrote:
> >> As a side note to what you were saying in the .sh file it would be
> really
> >> nice if the shp2pgsql dealt with standalone dbf files too. I mean the
> logic
> >> is all there so doesn't seem like it would be that hard to put in a
> switch
> >> for that.
> >>
> >> For the dbfs I was using Ogr2Ogr which works well except it adds a
> useless
> >> geometry field.
> >>
> >>
> >>
> >> -----Original Message-----
> >> From: postgis-users-bounces at postgis.refractions.net
> >> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Stephen
> >> Frost
> >> Sent: Thursday, April 03, 2008 7:16 PM
> >> To: PostGIS Users Discussion
> >> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> >>
> >> * Stephen Frost (sfrost at snowman.net) wrote:
> >>> If anyone's interested in the scripts used to load the data (they're
> >>> pretty simple, really), I'd be happy to provide them.
> >>
> >> Alright, since it's apparently kind of popular, I went ahead and put
> the
> >> script up here:
> >>
> >> http://snowman.net/tiger/
> >>
> >> I also included a README.txt that is essentially what I wrote up to the
> >> first person who asked me for the script. Please let me know if you
> improve
> >> upon it or find problems with it.
> >>
> >> Thanks,
> >>
> >> Stephen
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 189 bytes
> Desc: Digital signature
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/366a2d47/attachment-0001.bin
>
> ------------------------------
>
> Message: 25
> Date: Thu, 3 Apr 2008 21:10:17 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: RE: [postgis-users] TIGER/Line Shapefiles released
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <00a801c895f0$a57ef630$4d812e40 at l>
> Content-Type: text/plain; charset="us-ascii"
>
> Thanks I'll take a look. I was thinking more for completeness and the
> marginal effort that I think would be involved from my memory of what the
> shp2pgsql C code looks like. I would change it myself, but I'm moderately
> afraid of C code.
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Stephen
> Woodbridge
> Sent: Thursday, April 03, 2008 9:53 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
>
> Yes, it would be ideal if it was handled by shp2pgsql but have you looked
> at:
>
>
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&
> btnG=Search<http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search>
>
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&b
> tnG=Search<http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search>
>
> http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&bt
> nG=Search<http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search>
>
> some of these might be helpful.
>
> Best regards,
> -Stephen Woodbridge
> http://imaptools.com/
>
> Paragon Corporation wrote:
> > As a side note to what you were saying in the .sh file it would be
> > really nice if the shp2pgsql dealt with standalone dbf files too. I
> > mean the logic is all there so doesn't seem like it would be that hard
> > to put in a switch for that.
> >
> > For the dbfs I was using Ogr2Ogr which works well except it adds a
> > useless geometry field.
> >
> >
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> > Stephen Frost
> > Sent: Thursday, April 03, 2008 7:16 PM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> >
> > * Stephen Frost (sfrost at snowman.net) wrote:
> >> If anyone's interested in the scripts used to load the data (they're
> >> pretty simple, really), I'd be happy to provide them.
> >
> > Alright, since it's apparently kind of popular, I went ahead and put
> > the script up here:
> >
> > http://snowman.net/tiger/
> >
> > I also included a README.txt that is essentially what I wrote up to
> > the first person who asked me for the script. Please let me know if
> > you improve upon it or find problems with it.
> >
> > Thanks,
> >
> > Stephen
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 26
> Date: Thu, 03 Apr 2008 21:48:38 -0500
> From: Stephen Woodbridge <woodbri at swoodbridge.com>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <47F59706.6090409 at swoodbridge.com>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> Stephen Frost wrote:
> > Stephen,
> >
> > * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> >> I had an extensive discussion with some of the Census Geography staff
> >> about the Island provinces and Hawaii because I was trying to align
> >> Navteq routes over Tiger data and the Navteq routes were about a .25-.5
> >> miles east of the Tiger data in Hawaii.
> >
> > Interesting...
> >
> >> It turns out that they really have not idea what the "local datums" are
> >> that were originally used. I tried a lot of the local Island datums in
> >> the proj4 epsg file but could not find any close matches.
> >
> > In their documentation they talk about the datums used for these
> > regions.. Lemme go look up what they say..
> >
> > Here we go:
> >
> > North American Datum of 1983 in the 48 contiguous states, the District
> > of Columbia, Alaska, Hawaii (only Oahu Island within Honolulu County, HI
> > (15003)), Puerto Rico, and the U.S. Virgin Islands.
> >
> > Regional datums are used in the Pacific Island Areas (American Samoa,
> > Guam, and the Commonwealth of the Northern Mariana Islands) and most of
> > Hawaii.
> >
> > The datums used in the remainder of Hawaii are as follows: Hawaii
> > County, HI (15001), Old Hawaiian Datum; Honolulu County, HI (15003),
> > local astronomic datums for all islands northwest of 161 degrees west
> > longitude; Kalawao County, HI (15005), Old Hawaiian Datum; Kauai County,
> > HI (15007), Old Hawaiian Datum for Kauai Island and local astronomic
> > datum for Kaula Rock; Maui County, HI (15009), Old Hawaiian Datum.
> >
> > The datums used in American Samoa are as follows: Eastern District, AS
> > (60010), American Samoa Datum of 1962; Manu'a District, AS (60020),
> > American Samoa Datum of 1962; Rose Island, AS (60030), local astronomic
> > datum; Swains Island, AS (60040), local astronomic datum 1939; Western
> > District, AS (60050), American Samoa Datum of 1962.
> >
> > The Guam Datum of 1963 is used in Guam (66010). The datums used in the
> > Commonwealth of the Northern Mariana Islands are as follows: Northern
> > Islands Municipality (69085), Guam Datum of 1963 (Agrihan, Alamagan,
> > Anatahan, Gugan, Medinilla, Pagan, and Sarigan Islands) and local
> > astronomic datums (Asuncion, Maug, and Farallon De Pajaros Islands);
> > Rota Municipality (69100), Guam Datum of 1963; Saipan Municipality
> > (69110), Guam Datum of 1963; Tinian Municipality (69120), Guam Datum of
> > 1963.
> >
> > So, there's somewhat more information there than perhaps there was
> > previously, but I havn't gone hunting for, eg, "Old Hawaiian Datum". If
> > someone does come up with the correct datums/srids/etc based off of this
> > (or whatever), I'd be happy to update my script accordingly.
>
> Yeah, that is pretty much what they finally offered to me after a lot of
> research. I think some of this is newer info than I originally got. I
> would have to go digging for my notes, and this info about is probably
> more accurate at this point.
>
> I was primarily focused on the problem I had with the route alignments
> that we resolved by using the Navteq data for the maps when we were
> displaying routes.
>
> From proj4 epsg:
>
> # Old Hawaiian
> <4135> +proj=longlat +ellps=clrk66 +no_defs <>
>
> # American Samoa 1962
> <4169> +proj=longlat +ellps=clrk66 +towgs84=-115,118,426,0,0,0,0
> +no_defs <>
>
> From proj4 esri
>
> # GCS Guam 1963
> <37220> +proj=longlat +ellps=clrk66 no_defs <>
>
> Best regards,
> -Stephen Woodbridge
> http://imaptools.com/
>
> > Thanks!
> >
> > Stephen
> >
> >> It will be nice when they get that fixed up :)
> >>
> >> Stephen - thank you for sharing your scripts.
> >>
> >> Best regards,
> >> -Stephen Woodbridge
> >> http://imaptools.com/
> >>
> >>> Thanks!
> >>>
> >>> Stephen
> >>>
> >>>> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost wrote:
> >>>>> * Stephen Frost (sfrost at snowman.net) wrote:
> >>>>>> I think they may have also upgraded their pipe.. I got about
> 1.41MB/s
> >>>>>> (11 Mb/s) for the whole transfer. It's about 22G all told. I'll
> >>>>>> probably be trying to load it up into PG on one of our servers
> tomorrow.
> >>>>>> It was a bit over 4 hours for me to pull down off of their
> >>>>>> ftp2.census.gov ftp site.
> >>>>> Just to update those who might be interested- I've finished the data
> >>>>> load into one of our servers at work. It comes to ~60GB on disk in
> >>>>> PostgreSQL/PostGIS with appropriate indexes in most places and
> whatnot.
> >>>>> Based on what I've seen so far, it looks *very* nice, especially the
> >>>>> hydrogrophy ("areawater"). It also appears to be pretty consistant
> >>>>> across the layers, which is also good.
> >>>>>
> >>>>> If anyone's interested in the scripts used to load the data (they're
> >>>>> pretty simple, really), I'd be happy to provide them.
> >>>>>
> >>>>> Enjoy,
> >>>>>
> >>>>> Stephen
> >>>>> _______________________________________________
> >>>>> postgis-users mailing list
> >>>>> postgis-users at postgis.refractions.net
> >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>> _______________________________________________
> >>>> postgis-users mailing list
> >>>> postgis-users at postgis.refractions.net
> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>>
> >>>>
> ------------------------------------------------------------------------
> >>>>
> >>>> _______________________________________________
> >>>> postgis-users mailing list
> >>>> postgis-users at