[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 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: 27
> Date: Thu, 03 Apr 2008 22:40:18 -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: <47F5A322.3050802 at swoodbridge.com>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> Stephen Frost wrote:
> > * 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.
>
> Stephen,
>
> I added to process_tiger.sh:
>
> # User to connect to database with or use USER=$LOGNAME
> USER="postgres"
>
> and in vi did:
>
> %s/\<psql\>/psql -U $USER/g
>
> to update the script.
>
> Question on the README.txt
>
> -n Load national-level data
> -b Load state-level data
> -c Load county-level data
>
> Do the above options load all the data at the given level or do I also
> need to run it for each state and county with the options below?
>
> -s <2-digit state code> Individual state code to load
> -c <3-digit or 5-digit county code> Individual county code to load
>
> Does:
>
> cd /u/srcdata/tiger2007fe
> ./process_tiger.sh -n -b -c
>
> load all the data? Assuming that that directory is a mirror of the
> Census download directory.
>
> Thanks,
> -Stephen Woodbridge
> http://imaptools.com/
>
>
> ------------------------------
>
> Message: 28
> Date: Thu, 3 Apr 2008 22:54:08 -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: <20080404025408.GD4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Stephen,
>
> * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> > I added to process_tiger.sh:
> >
> > # User to connect to database with or use USER=$LOGNAME
> > USER="postgres"
>
> Ah, sure, makes sense. We user Kerberos for our authentication, so I
> tend to forget about user/pw authentication issues.
>
> > and in vi did:
> >
> > %s/\<psql\>/psql -U $USER/g
> >
> > to update the script.
>
> All of the options passed to psql really should be done as options to
> the script. I'll look at adding that (and -U support) sometime
> tomorrow.
>
> > Question on the README.txt
> >
> > -n Load national-level data
> > -b Load state-level data
> > -c Load county-level data
> >
> > Do the above options load all the data at the given level or do I also
> > need to run it for each state and county with the options below?
>
> Yes, by default it'll load all data at that level.
>
> > -s <2-digit state code> Individual state code to load
> > -c <3-digit or 5-digit county code> Individual county code to load
>
> These are used if you, for example, have to reload a specific state
> and/or county. I had the luxury of doing this when someone decided to
> restart the database in the middle of my data load.. :)
>
> > Does:
> >
> > cd /u/srcdata/tiger2007fe
> > ./process_tiger.sh -n -b -c
> >
> > load all the data? Assuming that that directory is a mirror of the
> > Census download directory.
>
> Yes, it should, though it expects to be run one level up from
> 'TIGER2007FE', unless you adjust the "BASE" environment variable at the
> top of the script.
>
> 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/6ba49bb6/attachment-0001.bin
>
> ------------------------------
>
> Message: 29
> Date: Fri, 04 Apr 2008 00:05:53 -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: <47F5B731.2080309 at swoodbridge.com>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> OK, Now I'm feeling kind of dumb.
>
> ./process_tiger.sh -n
> Internal error!
> ./process_tiger.sh -n \*
> Internal error!
> ./process_tiger.sh -n ''
> Internal error!
>
> woodbri at carto:~/work/new-tiger$ sh -x process_tiger.sh -d -n
> + BASE=TIGER2007FE
> + SETBASE=fe_2007
> + PREFIX=tiger
> + SKIP00=y
> ++ mktemp -d -p .
> + TMPDIR=./tmp.ZKN9vx
> + SRID=4269
> + USER=postgres
> + HOST=carto
> + DB=tiger2007fe
> + ENCODING=LATIN1
> + NATIONAL=false
> + STATELVL=false
> + STATES=
> + COUNTYLVL=false
> + COUNTIES=
> + DROP=false
> ++ getopt -o n::ls::bc::ed -n process_tiger.sh -- -d -n
> + TEMP= -d -n '' --
> + '[' 0 '!=' 0 ']'
> + eval set -- ' -d -n '\'''\'' --'
> ++ set -- -d -n '' --
> + true
> + DROP=true
> + shift
> + true
> + NATIONAL=true
> + shift
> + true
> + echo 'Internal error!'
> Internal error!
> + exit 1
>
> What did I miss here.
>
> -Steve
>
> Stephen Frost wrote:
> > Stephen,
> >
> > * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> >> I added to process_tiger.sh:
> >>
> >> # User to connect to database with or use USER=$LOGNAME
> >> USER="postgres"
> >
> > Ah, sure, makes sense. We user Kerberos for our authentication, so I
> > tend to forget about user/pw authentication issues.
> >
> >> and in vi did:
> >>
> >> %s/\<psql\>/psql -U $USER/g
> >>
> >> to update the script.
> >
> > All of the options passed to psql really should be done as options to
> > the script. I'll look at adding that (and -U support) sometime
> > tomorrow.
> >
> >> Question on the README.txt
> >>
> >> -n Load national-level data
> >> -b Load state-level data
> >> -c Load county-level data
> >>
> >> Do the above options load all the data at the given level or do I also
> >> need to run it for each state and county with the options below?
> >
> > Yes, by default it'll load all data at that level.
> >
> >> -s <2-digit state code> Individual state code to load
> >> -c <3-digit or 5-digit county code> Individual county code to load
> >
> > These are used if you, for example, have to reload a specific state
> > and/or county. I had the luxury of doing this when someone decided to
> > restart the database in the middle of my data load.. :)
> >
> >> Does:
> >>
> >> cd /u/srcdata/tiger2007fe
> >> ./process_tiger.sh -n -b -c
> >>
> >> load all the data? Assuming that that directory is a mirror of the
> >> Census download directory.
> >
> > Yes, it should, though it expects to be run one level up from
> > 'TIGER2007FE', unless you adjust the "BASE" environment variable at the
> > top of the script.
> >
> > Thanks,
> >
> > Stephen
> >
> >
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 30
> Date: Thu, 3 Apr 2008 21:34:47 -0700 (PDT)
> From: "kreshna_iceheart at yahoo.com" <kreshna_iceheart at yahoo.com>
> Subject: [postgis-users] Cannot display postGIS layers on ms4w 4.6.1
> To: postgis-users at postgis.refractions.net
> Message-ID: <168108.36541.qm at web56105.mail.re3.yahoo.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> I'm trying to display postGIS layers on MapServer. I
> am using the following components:
> 1) MapServer 4.6.1 for Windows (ms4w)
> 2) postgreSQL 8.2
> 3) shp2pgsql release 1.1.6
> 4) QuantumGIS 0.9.2 to automatically generate my
> mapfile
>
> My mapfile is attached with this email. It only has a
> single layer (for testing purpose), and it does not
> contain any complex query at all. The layer definition
> is as follows:
> =======================================================
> LAYER
> NAME 'batas_administrasi_line2_utm'
> TYPE LINE
> CONNECTIONTYPE postgis
> CONNECTION 'host=localhost user=rtrw
> dbname=rtrw-pacitan-trenggalek'
> DATA 'the_geom FROM
> batas-admin.batas_administrasi_line2_utm'
> =======================================================
>
>
> Yet, when I tried to display the mapfile, I got the
> following error message:
> =======================================================
> msDrawMap(): Image handling error. Failed to draw
> layer named 'batas_administrasi_line2_utm'.
> prepare_database(): Query error. Error executing
> POSTGIS DECLARE (the actual query) statement: 'DECLARE
> mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
> from batas-admin.batas_administrasi_line2_utm WHERE ()
> and (the_geom && setSRID( 'BOX3D(528656.510416667
> 9074209,609071.177083333
>
> 9134520)'::BOX3D,find_srid('','batas-admin.batas_administrasi_line2_utm','the_geom')
> ))'
>
> Postgresql reports the error as 'ERROR: syntax error
> at or near "-" at character 116 '
>
> More Help:
>
> Error with POSTGIS data variable. You specified
> '<check your .map file>'.
> Standard ways of specifiying are :
> (1) 'geometry_column from geometry_table'
> (2) 'geometry_column from (<sub query>) as foo using
> unique <column name> using SRID=<srid#>'
>
> Make sure you put in the 'using unique <column name>'
> and 'using SRID=#' clauses in.
>
> For more help, please see
> http://postgis.refractions.net/documentation.php
>
> Mappostgis.c - version of Jan 23/2004.
> =======================================================
>
> I have tried the following solution:
>
> (1) changing "FROM" to "from" (uppercase to lowercase)
>
> (2) enabling oid in my database:
> - uncommenting the line "default_with_oids" in my
> postgresql.on
> - changing the value to "on"
> - restarting posgresql
> - re-creating all my postGIS tables
>
>
> Yet none of the solution above work. MapServer just
> keeps giving me the error message.
>
> What happens? Anyone know a working solution?
>
> Thanks,
> -Kresh
>
>
>
>
>
> ____________________________________________________________________________________
> You rock. That's why Blockbuster's offering you one
> month of Blockbuster Total Access, No Cost.
> http://tc.deals.yahoo.com/tc/blockbuster/text5.com
>
> __________________________________________________________________
> So be it. If saying "NO" means being alone, then to hell with love, with
> women, with marriage, with God, religions, bars, nightclubs, computer games,
> and all the shit life keeps pumping at me. I'll walk alone, but with freedom
> and a healed pride.
>
>
>
> ____________________________________________________________________________________
> You rock. That's why Blockbuster's offering you one month of Blockbuster
> Total Access, No Cost.
> http://tc.deals.yahoo.com/tc/blockbuster/text5.com
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: rtrw-pacitan-trenggalek1.map
> Type: application/octet-stream
> Size: 2994 bytes
> Desc: 165038048-rtrw-pacitan-trenggalek1.map
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/38e12258/rtrw-pacitan-trenggalek1.dll
>
> ------------------------------
>
> Message: 31
> Date: Fri, 4 Apr 2008 01:37:24 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: RE: [postgis-users] Cannot display postGIS layers on ms4w
> 4.6.1
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <001701c89615$f6453730$50812e40 at r>
> Content-Type: text/plain; charset="us-ascii"
>
> Just a guess I think bata-admin as a schema is a bad choice of schema
> names.
> I think you have to quote it if you are going to use that. I would change
> it to rename your schema if I were you. To something like bata_admin
>
> If you go with what you have, I think you may need to quote it and I'm not
> even sure if that is legal in mapserver since I've never needed to quote
> my
> schemas.
>
> LAYER
> NAME 'batas_administrasi_line2_utm'
> TYPE LINE
> CONNECTIONTYPE postgis
> CONNECTION 'host=localhost user=rtrw dbname=rtrw-pacitan-trenggalek'
> DATA 'the_geom FROM
> "batas-admin".batas_administrasi_line2_utm'
>
> 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
> kreshna_iceheart at yahoo.com
> Sent: Friday, April 04, 2008 12:35 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Cannot display postGIS layers on ms4w 4.6.1
>
> I'm trying to display postGIS layers on MapServer. I am using the
> following
> components:
> 1) MapServer 4.6.1 for Windows (ms4w)
> 2) postgreSQL 8.2
> 3) shp2pgsql release 1.1.6
> 4) QuantumGIS 0.9.2 to automatically generate my mapfile
>
> My mapfile is attached with this email. It only has a single layer (for
> testing purpose), and it does not contain any complex query at all. The
> layer definition is as follows:
> =======================================================
> LAYER
> NAME 'batas_administrasi_line2_utm'
> TYPE LINE
> CONNECTIONTYPE postgis
> CONNECTION 'host=localhost user=rtrw dbname=rtrw-pacitan-trenggalek'
> DATA 'the_geom FROM
> batas-admin.batas_administrasi_line2_utm'
> =======================================================
>
>
> Yet, when I tried to display the mapfile, I got the following error
> message:
> =======================================================
> msDrawMap(): Image handling error. Failed to draw layer named
> 'batas_administrasi_line2_utm'.
> prepare_database(): Query error. Error executing POSTGIS DECLARE (the
> actual
> query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
> from batas-admin.batas_administrasi_line2_utm WHERE () and (the_geom &&
> setSRID( 'BOX3D(528656.510416667
> 9074209,609071.177083333
>
> 9134520)'::BOX3D,find_srid('','batas-admin.batas_administrasi_line2_utm','th
> e_geom')
> ))'
>
> Postgresql reports the error as 'ERROR: syntax error at or near "-" at
> character 116 '
>
> More Help:
>
> Error with POSTGIS data variable. You specified '<check your .map file>'.
> Standard ways of specifiying are :
> (1) 'geometry_column from geometry_table'
> (2) 'geometry_column from (<sub query>) as foo using unique <column name>
> using SRID=<srid#>'
>
> Make sure you put in the 'using unique <column name>'
> and 'using SRID=#' clauses in.
>
> For more help, please see
> http://postgis.refractions.net/documentation.php
>
> Mappostgis.c - version of Jan 23/2004.
> =======================================================
>
> I have tried the following solution:
>
> (1) changing "FROM" to "from" (uppercase to lowercase)
>
> (2) enabling oid in my database:
> - uncommenting the line "default_with_oids" in my postgresql.on
> - changing the value to "on"
> - restarting posgresql
> - re-creating all my postGIS tables
>
>
> Yet none of the solution above work. MapServer just keeps giving me the
> error message.
>
> What happens? Anyone know a working solution?
>
> Thanks,
> -Kresh
>
>
>
>
>
> ____________________________________________________________________________
> ________
> You rock. That's why Blockbuster's offering you one month of Blockbuster
> Total Access, No Cost.
> http://tc.deals.yahoo.com/tc/blockbuster/text5.com
>
> __________________________________________________________________
> So be it. If saying "NO" means being alone, then to hell with love, with
> women, with marriage, with God, religions, bars, nightclubs, computer
> games,
> and all the shit life keeps pumping at me. I'll walk alone, but with
> freedom
> and a healed pride.
>
>
>
>
> ____________________________________________________________________________
> ________
> You rock. That's why Blockbuster's offering you one month of Blockbuster
> Total Access, No Cost.
> http://tc.deals.yahoo.com/tc/blockbuster/text5.com
>
>
>
>
> ------------------------------
>
> Message: 32
> Date: Fri, 04 Apr 2008 10:24:21 +0200
> From: Barend Kobben <kobben at itc.nl>
> Subject: Re: [postgis-users] Problem with the Codification/charset
> from Postgis to mapserver
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <C41BB255.1BE1%kobben at itc.nl <C41BB255.1BE1%25kobben at itc.nl>>
> Content-Type: text/plain; charset="ISO-8859-1"
>
> .... also make sure Mapserevre connects as a UTF-8 client to PG by
> satining
> including in the CONNECTION object:
> options='-c client_encoding=UTF8'
>
>
> --
> Barend Köbben
> International Institute for Geo-Information
> Sciences and Earth Observation (ITC)
> PO Box 6
> 7500AA Enschede, The Netherlands
> +31 (0)53 4874253
>
>
>
> On 03-04-2008 18:10, "Stephen Woodbridge" <woodbri at swoodbridge.com> wrote:
>
> > Emilio Ponce wrote:
> >> Hi everyone!
> >>
> >> Recently I've done a simple mapfile (mapserver) that gets a postgis
> >> table and represents it. The problem is about the codification: The
> >> Postgis database is coded as UTF8 and when I represent a varchar column
> >> with mapserver the accents and especial characters are wrong. What can
> I
> >> do? Can I easily change the mapserver codification?
> >
> > This is probably better posted to the mapserver list. But you might try
> > adding to your label block
> >
> > ENCODING "UTF-8"
> >
> > -Steve W
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> International Institute for Geo-Information Science and Earth Observation
> (ITC)
> Chamber of Commerce: 410 27 560
>
> E-mail disclaimer
> The information in this e-mail, including any attachments, is intended for
> the addressee only. If you are not the intended recipient, you are hereby
> notified that any disclosure, copying, distribution or action in relation to
> the content of this information is strictly prohibited. If you have received
> this e-mail by mistake, please delete the message and any attachment and
> inform the sender by return e-mail. ITC accepts no liability for any error
> or omission in the message content or for damage of any kind that may arise
> as a result of e-mail transmission.
>
>
> ------------------------------
>
> Message: 33
> Date: Fri, 04 Apr 2008 08:01:31 +0000
> 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: <1207296091.3135.22.camel at localhost.localdomain>
> Content-Type: text/plain
>
> On Thu, 2008-04-03 at 20:31 -0400, Stephen Frost wrote:
> > 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..)
>
> I haven't yet confirmed whether the 2007 version of Census Blocks
> maintains a 1:1 match with the associated statistics in SF1, so was
> starting with the 2000 version. However, positionally, the two Census
> Block versions (tabblock00 and tabblock) are identical. And they both
> positionally match the single 2007 edges data. (I've added a third
> screen shot to www.giswebsite.com/demos/tiger_overlays.html to
> illustrate.)
>
> Has anyone else seen the same misalignment between TIGER (2007 and/or
> 2000) when overlaying on other datasets?
>
> It's still a mystery how OpenStreetMap's tiles for Berkeley, which are
> based on TIGER, don't seem to duplicate the zig-zag qualities of the
> edges data I've downloaded from TIGER 2007. Something is definitely
> missing in the puzzle, but it doesn't sound like the issue is with the
> PostGIS conversion part of the process, so, thanks for the help to this
> point -- I'll check with the OpenStreetMap community next.
>
> >
> > 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
>
>
>
> ------------------------------
>
> Message: 34
> Date: Fri, 4 Apr 2008 07:04:50 -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: <20080404110450.GF4999 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Steve,
>
> * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> > OK, Now I'm feeling kind of dumb.
>
> You shouldn't, that was my goof. :)
>
> > ++ getopt -o n::ls::bc::ed -n process_tiger.sh -- -d -n
>
> That should have been 'nl::s::...'. I've fixed that in my script,
> please give it another go. I made some changes (like adding parameters,
> heh) after I got the data loaded and so havn't completely retested
> everything. Actually, I've made some further updates/improvments which
> will help (like, I dunno, error checking...).
>
> 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/20080404/e6024002/attachment-0001.bin
>
> ------------------------------
>
> Message: 35
> Date: Fri, 4 Apr 2008 14:52:38 +0300
> From: "Nick Black" <nickblack1 at gmail.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:
> <223020e60804040452i15f6e3b3qd84ec37225edb170 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> On Fri, Apr 4, 2008 at 11:01 AM, Jonathan W. Lowe <jlowe at giswebsite.com>
> wrote:
> > On Thu, 2008-04-03 at 20:31 -0400, Stephen Frost wrote:
> > > Jonathan,
> > >
> > > * Jonathan W. Lowe (jlowe at giswebsite.com) wrote:
> >
> >...
>
> [Message clipped]
--
Dylan Lorimer | Strategic Partner Management
415.573.2909 (Grand Central) | 650.644.0182 (Fax)
If you received this communication by mistake, please don't forward it to
anyone else (it may contain confidential or privileged information), please
erase all copies of it, including all attachments, and please let the sender
know it went to the wrong person. Thanks.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080414/06982c60/attachment.html>
More information about the postgis-users
mailing list