<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16640" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008>Dylan,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008>It is best not to reply to a Digest
message. Anyrate - I'm not sure this is the best way to do this, but I
would assume the below should do the trick</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=017092600-15042008>SELECT
A.*<BR>FROM A LEFT JOIN B ON ST_Within(A.the_geom, B.the_geom)<BR>
LEFT JOIN C ON ST_Within(A.the_geom,
C.the_geom)<BR><BR>WHERE B.gid IS NULL AND C.gid IS NULL;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008>Should do the trick. </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008>Hope that helps,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=017092600-15042008>Regina</SPAN></FONT></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Dylan
Lorimer<BR><B>Sent:</B> Monday, April 14, 2008 4:41 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Re:
postgis-users Digest, Vol 66, Issue 4<BR></FONT><BR></DIV>
<DIV></DIV>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:<BR><BR>Given Regin's suggested LEFT JOIN:<BR><BR>SELECT
A.*<BR>FROM A LEFT JOIN B ON ST_Intersects(A.the_geom,
B.the_geom)<BR> LEFT JOIN C ON
ST_Intersects(A.the_geom, C.the_geom)<BR><BR>WHERE B.gid IS NULL AND C.gid IS
NULL;<BR><BR>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. <BR><BR>Cheers,<BR>dylan<BR><BR><BR><BR><BR><BR>
<DIV class=gmail_quote>On Fri, Apr 4, 2008 at 12:00 PM, <<A
href="mailto:postgis-users-request@postgis.refractions.net">postgis-users-request@postgis.refractions.net</A>>
wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Send
postgis-users mailing list submissions to<BR> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><BR>To
subscribe or unsubscribe via the World Wide Web, visit<BR>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>or,
via email, send a message with subject or body 'help' to<BR>
<A
href="mailto:postgis-users-request@postgis.refractions.net">postgis-users-request@postgis.refractions.net</A><BR><BR>You
can reach the person managing the list at<BR> <A
href="mailto:postgis-users-owner@postgis.refractions.net">postgis-users-owner@postgis.refractions.net</A><BR><BR>When
replying, please edit your Subject line so it is more specific<BR>than "Re:
Contents of postgis-users digest..."<BR><BR><BR>Today's Topics:<BR><BR>
1. Re: TIGER/Line Shapefiles released (Stephen Frost)<BR> 2. Invoking
GIST index on 2 disjoints within a query (Dylan Lorimer)<BR> 3. Re:
Invoking GIST index on 2 disjoints within a query<BR> (Paul
Ramsey)<BR> 4. Re: TIGER/Line Shapefiles released (Jeshua
Lacock)<BR> 5. Re: Invoking GIST index on 2 disjoints within a
query<BR> (Kevin Neufeld)<BR> 6. RE: TIGER/Line
Shapefiles released (Paragon Corporation)<BR> 7. TIGER/LINE Shapefiles
SMID mapping (Paragon Corporation)<BR> 8. Re: Invoking GIST index on 2
disjoints within a query (nw)<BR> 9. RE: Invoking GIST index on 2
disjoints within a query<BR> (Paragon
Corporation)<BR> 10. Re: TIGER/Line Shapefiles released (Jonathan W.
Lowe)<BR> 11. Re: postgis (Colin Wetherbee)<BR> 12. Re: TIGER/Line
Shapefiles released (Frank Durstewitz)<BR> 13. Re: TIGER/Line Shapefiles
released (Jonathan W. Lowe)<BR> 14. Re: TIGER/Line Shapefiles released
(Stephen Frost)<BR> 15. Re: TIGER/Line Shapefiles released (Stephen
Frost)<BR> 16. RE: TIGER/Line Shapefiles released (Paragon
Corporation)<BR> 17. Re: TIGER/Line Shapefiles released (Jonathan W.
Lowe)<BR> 18. Re: TIGER/Line Shapefiles released (Ron M)<BR> 19. Re:
TIGER/Line Shapefiles released (Jonathan W. Lowe)<BR> 20. Re: TIGER/Line
Shapefiles released (Stephen Frost)<BR> 21. Re: TIGER/Line Shapefiles
released (Stephen Woodbridge)<BR> 22. Re: TIGER/Line Shapefiles released
(Stephen Woodbridge)<BR> 23. Re: TIGER/Line Shapefiles released (Stephen
Frost)<BR> 24. Re: TIGER/Line Shapefiles released (Stephen
Frost)<BR> 25. RE: TIGER/Line Shapefiles released (Paragon
Corporation)<BR> 26. Re: TIGER/Line Shapefiles released (Stephen
Woodbridge)<BR> 27. Re: TIGER/Line Shapefiles released (Stephen
Woodbridge)<BR> 28. Re: TIGER/Line Shapefiles released (Stephen
Frost)<BR> 29. Re: TIGER/Line Shapefiles released (Stephen
Woodbridge)<BR> 30. Cannot display postGIS layers on ms4w 4.6.1<BR>
(<A
href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</A>)<BR> 31.
RE: Cannot display postGIS layers on ms4w 4.6.1<BR>
(Paragon Corporation)<BR> 32. Re: Problem with the
Codification/charset from Postgis to<BR>
mapserver (Barend Kobben)<BR> 33. Re: TIGER/Line Shapefiles
released (Jonathan W. Lowe)<BR> 34. Re: TIGER/Line Shapefiles released
(Stephen Frost)<BR> 35. Re: TIGER/Line Shapefiles released (Nick
Black)<BR> 36. Re: TIGER/Line Shapefiles released (Stephen
Frost)<BR> 37. Re: TIGER/Line Shapefiles released (Jonathan W.
Lowe)<BR> 38. Re: TIGER/Line Shapefiles released (Nick
Black)<BR> 39. ERROR: Operation on mixed SRID geometries after
upgrading to<BR> recent postgres/postgis (Sebastian
Reitenbach)<BR> 40. RE: ERROR: Operation on mixed SRID geometries
afterupgrading<BR> to recent postgres/postgis (Obe,
Regina)<BR> 41. Re: TIGER/Line Shapefiles released (Stephen
Woodbridge)<BR> 42. RE: ERROR: Operation on mixed SRID geometries
afterupgrading<BR> to recent postgres/postgis
(Sebastian Reitenbach)<BR> 43. FOSS4G 2008 Call for Workshops / Papers
(Paul Ramsey)<BR> 44. Loading Tiger2007fe data - Thoughts on using
inherited tables<BR> (Stephen
Woodbridge)<BR> 45. Re: Loading Tiger2007fe data - Thoughts on using
inherited<BR> tables (Stephen Frost)<BR> 46. Re:
Loading Tiger2007fe data - Thoughts on using inherited<BR>
tables (Paul Ramsey)<BR> 47. Re: Loading Tiger2007fe data -
Thoughts on using inherited<BR> tables (Stephen
Frost)<BR><BR><BR>----------------------------------------------------------------------<BR><BR>Message:
1<BR>Date: Thu, 3 Apr 2008 17:07:46 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080403210746.GV4999@tamriel.snowman.net">20080403210746.GV4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>* Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> I
think they may have also upgraded their pipe.. I got about
1.41MB/s<BR>> (11 Mb/s) for the whole transfer. It's about 22G all
told. I'll<BR>> probably be trying to load it up into PG on one of
our servers tomorrow.<BR>> It was a bit over 4 hours for me to pull down
off of their<BR>> <A href="http://ftp2.census.gov"
target=_blank>ftp2.census.gov</A> ftp site.<BR><BR>Just to update those who
might be interested- I've finished the data<BR>load into one of our servers at
work. It comes to ~60GB on disk in<BR>PostgreSQL/PostGIS with
appropriate indexes in most places and whatnot.<BR>Based on what I've seen so
far, it looks *very* nice, especially the<BR>hydrogrophy ("areawater").
It also appears to be pretty consistant<BR>across the layers, which is
also good.<BR><BR>If anyone's interested in the scripts used to load the data
(they're<BR>pretty simple, really), I'd be happy to provide
them.<BR><BR> Enjoy,<BR><BR>
Stephen<BR>-------------- next part
--------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/bb74af0c/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/bb74af0c/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
2<BR>Date: Thu, 3 Apr 2008 16:12:38 -0500<BR>From: "Dylan Lorimer" <<A
href="mailto:edylan@google.com">edylan@google.com</A>><BR>Subject:
[postgis-users] Invoking GIST index on 2 disjoints within a<BR>
query<BR>To: "PostGIS Users Discussion" <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:<BR>
<<A
href="mailto:3ddac38d0804031412h16e7241fu2c3823a0e798edf6@mail.gmail.com">3ddac38d0804031412h16e7241fu2c3823a0e798edf6@mail.gmail.com</A>><BR>Content-Type:
text/plain; charset=ISO-8859-1<BR><BR>Hi,<BR><BR>So I have probably a simple
question but I don't use PostGIS enough to<BR>know the right way to go about
this. Hoping someone can help.<BR><BR>So I've got 3 spatial tables, each
containing a bunch of polygon<BR>geometries. Let's call the tables A, B, C.
What I want is to find out<BR>which polygons in table A are disjoint from all
polygons in tables B<BR>and C. Meaning, which polygons in A have 0 overlap
with polygons in B<BR>or C.<BR><BR>I seem to have no troubles doing this when
using only 2 tables, but<BR>adding the 3rd is messing things up. Oh, and I
also want to invoke the<BR>GIST indexes to speed things up.<BR><BR>Here's the
(slightly generalized) query I think I should be using:<BR><BR>SELECT <some
stuff> FROM A, B, C WHERE disjoint(A.the_geom,<BR>B.the_geom) AND
disjoint(A.the_geom, C.the_geom) AND <some other<BR>attribute filters>
AND A.the_geom && B.the_geom AND A.the_geom
&&<BR>C.the_geom;<BR><BR>I've not seen this query complete
successfully as it takes so long,<BR>but I think something is wrong with it as
running it against only A<BR>and B is really quick and there aren't too many
geometries in
the<BR>tables.<BR><BR>Thoughts?<BR><BR>Cheers,<BR>dylan<BR><BR><BR>------------------------------<BR><BR>Message:
3<BR>Date: Thu, 3 Apr 2008 14:23:27 -0700<BR>From: "Paul Ramsey" <<A
href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</A>><BR>Subject:
Re: [postgis-users] Invoking GIST index on 2 disjoints within<BR>
a query<BR>To: "PostGIS Users Discussion" <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:<BR>
<<A
href="mailto:30fe546d0804031423k3169a38at3b02346530e3fe80@mail.gmail.com">30fe546d0804031423k3169a38at3b02346530e3fe80@mail.gmail.com</A>><BR>Content-Type:
text/plain; charset=ISO-8859-1<BR><BR>Drop the &&. The indexes are not
much help for testing Disjoint, unfortunately.<BR><BR>The other thing you
could do is re-cast it as a does-not-intersect<BR>test
instead...<BR><BR>SELECT <some stuff> FROM A, B, C WHERE NOT (
ST_Intersects(A.the_geom,<BR>B.the_geom) OR ST_Intersects(A.the_geom,
C.the_geom) );<BR><BR>(The above will be indexed automatically for recent
versions of<BR>PostGIS. For older, add in the && clause manually like
this.)<BR><BR>SELECT <some stuff> FROM A, B, C WHERE NOT ( (A.the_geom
&& B.the_geom<BR>AND Intersects(A.the_geom, B.the_geom)) OR
(A.the_geom && c.the_geom<BR>AND ST_Intersects(A.the_geom, C.the_geom)
) );<BR><BR>YMMV... the B and C tables might be interacting and blowing up
the<BR>query, I don't have an intuitive feel for this stuff like Regina
:)<BR><BR>P.<BR><BR>On Thu, Apr 3, 2008 at 2:12 PM, Dylan Lorimer <<A
href="mailto:edylan@google.com">edylan@google.com</A>> wrote:<BR>>
Hi,<BR>><BR>> So I have probably a simple question but I don't use
PostGIS enough to<BR>> know the right way to go about this. Hoping
someone can help.<BR>><BR>> So I've got 3 spatial tables, each
containing a bunch of polygon<BR>> geometries. Let's call the tables
A, B, C. What I want is to find out<BR>> which polygons in table A
are disjoint from all polygons in tables B<BR>> and C. Meaning, which
polygons in A have 0 overlap with polygons in B<BR>> or
C.<BR>><BR>> I seem to have no troubles doing this when using only
2 tables, but<BR>> adding the 3rd is messing things up. Oh, and I
also want to invoke the<BR>> GIST indexes to speed things
up.<BR>><BR>> Here's the (slightly generalized) query I think I
should be using:<BR>><BR>> SELECT <some stuff> FROM A, B, C
WHERE disjoint(A.the_geom,<BR>> B.the_geom) AND disjoint(A.the_geom,
C.the_geom) AND <some other<BR>> attribute filters> AND
A.the_geom && B.the_geom AND A.the_geom &&<BR>>
C.the_geom;<BR>><BR>> I've not seen this query complete
successfully as it takes so long,<BR>> but I think something is wrong
with it as running it against only A<BR>> and B is really quick and
there aren't too many geometries in the<BR>> tables.<BR>><BR>>
Thoughts?<BR>><BR>> Cheers,<BR>> dylan<BR>>
_______________________________________________<BR>>
postgis-users mailing list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR><BR><BR>------------------------------<BR><BR>Message:
4<BR>Date: Thu, 3 Apr 2008 15:33:07 -0600<BR>From: Jeshua Lacock
<jeshua@3DTOPO.com><BR>Subject: Re: [postgis-users] TIGER/Line
Shapefiles released<BR>To: PostGIS Users Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>>,<BR>
Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Message-ID:
<C1CC27EC-9092-4FF3-B63E-4C22431211B7@3DTOPO.com><BR>Content-Type:
text/plain; charset=US-ASCII; format=flowed; delsp=yes<BR><BR><BR>On Apr 3,
2008, at 3:07 PM, Stephen Frost wrote:<BR><BR>> If anyone's interested in
the scripts used to load the data (they're<BR>> pretty simple, really), I'd
be happy to provide them<BR><BR>Hello Stephen,<BR><BR>Sure; I'll bite. No
sense reinventing the wheel if it can be helped.<BR>If it is more appropriate,
please feel free to email me off list.<BR><BR><BR>Thanks,<BR><BR>Jeshua
Lacock<BR>Founder/Programmer<BR>3DTOPO Incorporated<BR><<A
href="http://3DTOPO.com" target=_blank>http://3DTOPO.com</A>><BR>Phone:
877.240.1364<BR><BR><BR><BR>------------------------------<BR><BR>Message:
5<BR>Date: Thu, 03 Apr 2008 14:35:47 -0700<BR>From: Kevin Neufeld <<A
href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</A>><BR>Subject:
Re: [postgis-users] Invoking GIST index on 2 disjoints within<BR>
a query<BR>To: PostGIS Users Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F54DB3.4070802@refractions.net">47F54DB3.4070802@refractions.net</A>><BR>Content-Type:
text/plain; charset=ISO-8859-1; format=flowed<BR><BR>Hi Dylan,<BR><BR>You
could try something like this:<BR>SELECT <some stuff><BR>FROM
a<BR> LEFT JOIN b ON (ST_Intersects(a.the_geom,
b.the_geom))<BR> LEFT JOIN c ON (ST_Intersects(a.the_geom,
c.the_geom))<BR>WHERE b.the_geom IS NULL<BR>AND c.the_geom IS NULL;<BR><BR>--
Kevin<BR><BR>Dylan Lorimer wrote:<BR>> Hi,<BR>><BR>> So I have
probably a simple question but I don't use PostGIS enough to<BR>> know the
right way to go about this. Hoping someone can help.<BR>><BR>> So I've
got 3 spatial tables, each containing a bunch of polygon<BR>> geometries.
Let's call the tables A, B, C. What I want is to find out<BR>> which
polygons in table A are disjoint from all polygons in tables B<BR>> and C.
Meaning, which polygons in A have 0 overlap with polygons in B<BR>> or
C.<BR>><BR>> I seem to have no troubles doing this when using only 2
tables, but<BR>> adding the 3rd is messing things up. Oh, and I also want
to invoke the<BR>> GIST indexes to speed things up.<BR>><BR>> Here's
the (slightly generalized) query I think I should be using:<BR>><BR>>
SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,<BR>>
B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other<BR>>
attribute filters> AND A.the_geom && B.the_geom AND A.the_geom
&&<BR>> C.the_geom;<BR>><BR>> I've not seen this query
complete successfully as it takes so long,<BR>> but I think something is
wrong with it as running it against only A<BR>> and B is really quick and
there aren't too many geometries in the<BR>> tables.<BR>><BR>>
Thoughts?<BR>><BR>> Cheers,<BR>> dylan<BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR><BR><BR>------------------------------<BR><BR>Message:
6<BR>Date: Thu, 3 Apr 2008 17:47:14 -0400<BR>From: "Paragon Corporation"
<<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject: RE:
[postgis-users] TIGER/Line Shapefiles released<BR>To: "'PostGIS Users
Discussion'"<BR> <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>>,
"'Stephen Frost'"<BR>
<<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Message-ID:
<000e01c895d4$47c29900$4d812e40@l><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>I would be interested
too.<BR><BR>Thanks,<BR>Regina<BR><BR>-----Original Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Jeshua<BR>Lacock<BR>Sent: Thursday, April 03, 2008 5:33 PM<BR>To:
PostGIS Users Discussion; Stephen Frost<BR>Subject: Re: [postgis-users]
TIGER/Line Shapefiles released<BR><BR><BR>On Apr 3, 2008, at 3:07 PM, Stephen
Frost wrote:<BR><BR>> If anyone's interested in the scripts used to load
the data (they're<BR>> pretty simple, really), I'd be happy to provide
them<BR><BR>Hello Stephen,<BR><BR>Sure; I'll bite. No sense reinventing the
wheel if it can be helped.<BR>If it is more appropriate, please feel free to
email me off list.<BR><BR><BR>Thanks,<BR><BR>Jeshua
Lacock<BR>Founder/Programmer<BR>3DTOPO Incorporated<BR><<A
href="http://3DTOPO.com" target=_blank>http://3DTOPO.com</A>><BR>Phone:
877.240.1364<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
7<BR>Date: Thu, 3 Apr 2008 17:51:47 -0400<BR>From: "Paragon Corporation"
<<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject:
[postgis-users] TIGER/LINE Shapefiles SMID mapping<BR>To: "'PostGIS Users
Discussion'"<BR> <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<001201c895d4$ea384220$4d812e40@l><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>I've been looking at the TIGER/LINE
2007 shapefiles too and I notice that<BR>they have a field called SMID
(Spatial Metadata Identifier) in the edges<BR>table. I
thought these were supposed to be defined in the edges.shp.xml<BR>file or in
the PDF help, but on closer inspection of that file I see this<BR><BR>-
<attr><BR> <attrlabl>SMID</attrlabl><BR> <attrdef>Spatial
metadata identifier</attrdef><BR> <attrdefs>U.S. Census
Bureau</attrdefs><BR>- <attrdomv><BR>-
<edom><BR> <edomv>1</edomv><BR> <edomvd>Spatial
MID is positive, non-zero number</edomvd><BR> <edomvds>U.S.
Census
Bureau</edomvds><BR> </edom><BR> </attrdomv><BR> </attr><BR><BR><BR>Well
duh? I loaded all of Florida and there are 69 distinct values in that<BR>and I
have no clue what they mean. Does any one by chance know where
these<BR>codes are
defined?<BR><BR>Thanks,<BR>Regina<BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
8<BR>Date: Thu, 3 Apr 2008 17:01:43 -0500<BR>From: nw <<A
href="mailto:nw@hydaspes.if.org">nw@hydaspes.if.org</A>><BR>Subject: Re:
[postgis-users] Invoking GIST index on 2 disjoints within<BR>
a query<BR>To: PostGIS Users Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:8913B9C0-76F3-467E-8809-EEDAD73A15A3@hydaspes.if.org">8913B9C0-76F3-467E-8809-EEDAD73A15A3@hydaspes.if.org</A>><BR>Content-Type:
text/plain; charset=US-ASCII; format=flowed<BR><BR>On Apr 3, 2008, at 16:12,
Dylan Lorimer wrote:<BR><BR> > What I want is to find out which
polygons in table A<BR> > are disjoint from all polygons in tables B
and C.<BR><BR>> Here's the (slightly generalized) query I think I should be
using:<BR>><BR>> SELECT <some stuff> FROM A, B, C WHERE
disjoint(A.the_geom,<BR>> B.the_geom) AND disjoint(A.the_geom, C.the_geom)
AND <some other<BR>> attribute filters> AND A.the_geom &&
B.the_geom AND A.the_geom &&<BR>> C.the_geom;<BR>><BR>> I've
not seen this query complete successfully as it takes so long,<BR>> but I
think something is wrong with it as running it against only A<BR>> and B is
really quick and there aren't too many geometries in the<BR>>
tables.<BR>><BR>> Thoughts?<BR><BR>Use a union.<BR><BR>select <some
stuff> from <working a vs b query><BR>union<BR>select <some
compatible stuff> from <working a vs c
query><BR>;<BR><BR>--<BR>nw<BR><A
href="mailto:nw@hydaspes.if.org">nw@hydaspes.if.org</A><BR><BR><BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
9<BR>Date: Thu, 3 Apr 2008 18:14:37 -0400<BR>From: "Paragon Corporation"
<<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject: RE:
[postgis-users] Invoking GIST index on 2 disjoints within<BR>
a query<BR>To: "'PostGIS Users Discussion'"<BR>
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<000201c895d8$1b153b70$4d812e40@l><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>If I understand you correctly, then I
think the most efficient route is to<BR>figure out which ones overlap with an
Intersect as Paul mentioned, and then<BR>throw them out. I presume you
mean intersect rather than overlaps - ie.<BR>Overlaps means it can't be
contained within where as intersects can be.<BR>Either way flip st_intersect
with st_overlaps if you really want overlap<BR>check.<BR><BR>SELECT
A.*<BR>FROM A LEFT JOIN B ON ST_Intersects(A.the_geom,
B.the_geom)<BR> LEFT JOIN C ON
ST_Intersects(A.the_geom, C.the_geom)<BR><BR>WHERE B.gid IS NULL AND C.gid IS
NULL;<BR><BR>Hope that helps,<BR>Regina<BR><BR><BR><BR>-----Original
Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Paul<BR>Ramsey<BR>Sent: Thursday, April 03, 2008 5:23 PM<BR>To:
PostGIS Users Discussion<BR>Subject: Re: [postgis-users] Invoking GIST index
on 2 disjoints within a<BR>query<BR><BR>Drop the &&. The indexes are
not much help for testing Disjoint,<BR>unfortunately.<BR><BR>The other thing
you could do is re-cast it as a does-not-intersect
test<BR>instead...<BR><BR>SELECT <some stuff> FROM A, B, C WHERE NOT (
ST_Intersects(A.the_geom,<BR>B.the_geom) OR ST_Intersects(A.the_geom,
C.the_geom) );<BR><BR>(The above will be indexed automatically for recent
versions of PostGIS. For<BR>older, add in the && clause manually like
this.)<BR><BR>SELECT <some stuff> FROM A, B, C WHERE NOT ( (A.the_geom
&& B.the_geom AND<BR>Intersects(A.the_geom, B.the_geom)) OR
(A.the_geom && c.the_geom AND<BR>ST_Intersects(A.the_geom, C.the_geom)
) );<BR><BR>YMMV... the B and C tables might be interacting and blowing up the
query, I<BR>don't have an intuitive feel for this stuff like Regina
:)<BR><BR>P.<BR><BR>On Thu, Apr 3, 2008 at 2:12 PM, Dylan Lorimer <<A
href="mailto:edylan@google.com">edylan@google.com</A>> wrote:<BR>>
Hi,<BR>><BR>> So I have probably a simple question but I don't use
PostGIS enough<BR>> to know the right way to go about this. Hoping
someone can help.<BR>><BR>> So I've got 3 spatial tables, each
containing a bunch of polygon<BR>> geometries. Let's call the tables A, B,
C. What I want is to find out<BR>> which polygons in table A are disjoint
from all polygons in tables B<BR>> and C. Meaning, which polygons in A have
0 overlap with polygons in B<BR>> or C.<BR>><BR>> I seem to
have no troubles doing this when using only 2 tables, but<BR>> adding the
3rd is messing things up. Oh, and I also want to invoke the<BR>> GIST
indexes to speed things up.<BR>><BR>> Here's the (slightly
generalized) query I think I should be using:<BR>><BR>> SELECT
<some stuff> FROM A, B, C WHERE disjoint(A.the_geom,<BR>>
B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some
other<BR>> attribute filters> AND A.the_geom && B.the_geom AND
A.the_geom &&<BR>> C.the_geom;<BR>><BR>> I've not seen
this query complete successfully as it takes so long,<BR>> but I think
something is wrong with it as running it against only A<BR>> and B is
really quick and there aren't too many geometries in the<BR>>
tables.<BR>><BR>> Thoughts?<BR>><BR>> Cheers,<BR>>
dylan<BR>>
_______________________________________________<BR>>
postgis-users mailing list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
10<BR>Date: Thu, 03 Apr 2008 23:40:57 +0100<BR>From: "Jonathan W. Lowe" <<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<1207262457.4621.6.camel@localhost.localdomain><BR>Content-Type:
text/plain<BR><BR>Stephen,<BR><BR>Have you yet tried overlaying TIGER 2007
linework or census block/tract<BR>polygons over Google or OpenStreetMap tiles?
I'm seeing a good match in<BR>some areas but a significant shift (~50
meters) in others. Thought it<BR>might be a datum conversion issue, but
can't seem to find a match.<BR><BR>Jonathan<BR><BR>On Thu, 2008-04-03 at 17:07
-0400, Stephen Frost wrote:<BR>> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> > I
think they may have also upgraded their pipe.. I got about
1.41MB/s<BR>> > (11 Mb/s) for the whole transfer. It's about 22G
all told. I'll<BR>> > probably be trying to load it up into PG on
one of our servers tomorrow.<BR>> > It was a bit over 4 hours for me to
pull down off of their<BR>> > <A href="http://ftp2.census.gov"
target=_blank>ftp2.census.gov</A> ftp site.<BR>><BR>> Just to update
those who might be interested- I've finished the data<BR>> load into one of
our servers at work. It comes to ~60GB on disk in<BR>>
PostgreSQL/PostGIS with appropriate indexes in most places and
whatnot.<BR>> Based on what I've seen so far, it looks *very* nice,
especially the<BR>> hydrogrophy ("areawater"). It also appears to be
pretty consistant<BR>> across the layers, which is also
good.<BR>><BR>> If anyone's interested in the scripts used to load the
data (they're<BR>> pretty simple, really), I'd be happy to provide
them.<BR>><BR>> Enjoy,<BR>><BR>>
Stephen<BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
11<BR>Date: Thu, 03 Apr 2008 18:45:02 -0400<BR>From: Colin Wetherbee <<A
href="mailto:cww@denterprises.org">cww@denterprises.org</A>><BR>Subject:
Re: [postgis-users] postgis<BR>To: PostGIS Users Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F55DEE.8050503@denterprises.org">47F55DEE.8050503@denterprises.org</A>><BR>Content-Type:
text/plain; charset=UTF-8; format=flowed<BR><BR>Gregory Williamson
wrote:<BR>> It's been a while since I used MMS, but IIRC it does need a
reference<BR>> to a unique id; OID is picked as a default if there is
none<BR>> specified.<BR><BR>Yes, this is true. It doesn't have to be
any special or meaningful<BR>unique ID, like an OID or a primary key, but
MapServer looks for those.<BR> Anything else must be explicitly
specified.<BR><BR>> I think you can put something in the DATA statement
like the<BR>> following, assuming that the table has "gid" as a unique id
(add a<BR>> serial column and populate it is an easy way to add such a
thing).<BR>> Then:<BR>><BR>> DATA "the_geom FROM (SELECT
table1.the_geom AS the_geom, table1.gid<BR>> AS gid, table2.data AS data
FROM table1 LEFT JOIN table2 ON <A href="http://table1.id"
target=_blank>table1.id</A><BR>> = <A href="http://table2.id"
target=_blank>table2.id</A>) AS new_table USING UNIQUE gid USING
SRID=-1"<BR>><BR>> Which I think will make the interface use "gid"
instead of trying for<BR>> OIDs, which are being deprecated in
PostgreSQL itself.<BR><BR>In general, that DATA statement looks
correct.<BR><BR>As another example, here's a DATA statement of
mine.<BR><BR> DATA "line FROM (<BR>
SELECT num, id, line<BR> FROM
jsview_journeyroutes<BR> WHERE userid =
%userid%<BR> ) AS foo USING UNIQUE id USING
SRID=4326"<BR><BR>It's always a good idea to set the USING SRID=xxxx to the
proper SRID<BR>for the data set.<BR><BR>> You can also dump the table data,
drop the table, recreate it with an<BR>> explicit "WITH OIDS" in the
table creation, reload the data and away<BR>> you go without changing the
MMS interface. But it's better to get rid<BR>> of OIDs where you can since
they are not reliablely unique.<BR><BR>Yes, it's better to get rid of OIDs.
Creating an UNIQUE SERIAL column<BR>is
preferable.<BR><BR>Colin<BR><BR><BR>------------------------------<BR><BR>Message:
12<BR>Date: Fri, 04 Apr 2008 00:58:45 +0200<BR>From: Frank Durstewitz <<A
href="mailto:frank.durstewitz@emporis.com">frank.durstewitz@emporis.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F56125.9050308@emporis.com">47F56125.9050308@emporis.com</A>><BR>Content-Type:
text/plain; charset="iso-8859-1"<BR><BR>Skipped content of type
multipart/alternative-------------- next part --------------<BR>A non-text
attachment was scrubbed...<BR>Name: smime.p7s<BR>Type:
application/x-pkcs7-signature<BR>Size: 3281 bytes<BR>Desc: S/MIME
Cryptographic Signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/d7c9cbe9/smime-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/d7c9cbe9/smime-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
13<BR>Date: Fri, 04 Apr 2008 00:09:54 +0100<BR>From: "Jonathan W. Lowe" <<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<1207264194.4621.8.camel@localhost.localdomain><BR>Content-Type:
text/plain<BR><BR>I'm interested as well.<BR>Thanks, Jonathan<BR><BR>On Fri,
2008-04-04 at 00:58 +0200, Frank Durstewitz wrote:<BR>> Me too,
please.<BR>><BR>> Regards, Frank<BR>><BR>> Paragon Corporation am
03.04.2008 23:47:<BR>> > I would be interested too.<BR>> ><BR>>
> Thanks,<BR>> > Regina<BR>> ><BR>> > -----Original
Message-----<BR>> > From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>
> [mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Jeshua<BR>> > Lacock<BR>> > Sent: Thursday, April 03,
2008 5:33 PM<BR>> > To: PostGIS Users Discussion; Stephen Frost<BR>>
> Subject: Re: [postgis-users] TIGER/Line Shapefiles released<BR>>
><BR>> ><BR>> > On Apr 3, 2008, at 3:07 PM, Stephen Frost
wrote:<BR>> ><BR>> ><BR>> > > If anyone's interested in
the scripts used to load the data (they're<BR>> > > pretty simple,
really), I'd be happy to provide them<BR>> > ><BR>> ><BR>>
> Hello Stephen,<BR>> ><BR>> > Sure; I'll bite. No sense
reinventing the wheel if it can be helped.<BR>> > If it is more
appropriate, please feel free to email me off list.<BR>> ><BR>>
><BR>> > Thanks,<BR>> ><BR>> > Jeshua Lacock<BR>> >
Founder/Programmer<BR>> > 3DTOPO Incorporated<BR>> > <<A
href="http://3DTOPO.com" target=_blank>http://3DTOPO.com</A>><BR>> >
Phone: 877.240.1364<BR>> ><BR>> >
_______________________________________________<BR>> > postgis-users
mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> ><BR>> >
_______________________________________________<BR>> > postgis-users
mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> ><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
14<BR>Date: Thu, 3 Apr 2008 19:15:54 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080403231553.GW4999@tamriel.snowman.net">20080403231553.GW4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>* Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> If
anyone's interested in the scripts used to load the data (they're<BR>>
pretty simple, really), I'd be happy to provide them.<BR><BR>Alright, since
it's apparently kind of popular, I went ahead and put the<BR>script up
here:<BR><BR><A href="http://snowman.net/tiger/"
target=_blank>http://snowman.net/tiger/</A><BR><BR>I also included a
README.txt that is essentially what I wrote up to the<BR>first person who
asked me for the script. Please let me know if you<BR>improve upon it or
find problems with it.<BR><BR> Thanks,<BR><BR>
Stephen<BR>-------------- next
part --------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/3bc528c7/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/3bc528c7/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
15<BR>Date: Thu, 3 Apr 2008 19:18:00 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: "Jonathan W. Lowe"
<<A href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>>,
PostGIS Users<BR> Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080403231800.GX4999@tamriel.snowman.net">20080403231800.GX4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>Jonathan,<BR><BR>* Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
Have you yet tried overlaying TIGER 2007 linework or census
block/tract<BR>> polygons over Google or OpenStreetMap tiles? I'm
seeing a good match in<BR>> some areas but a significant shift (~50 meters)
in others. Thought it<BR>> might be a datum conversion issue, but
can't seem to find a match.<BR><BR>I hadn't looked at the linework too much
yet or tried to overlay it.<BR>I'm curious where you're seeing the differences
though because I know<BR>that Census is only about half way through their MAF
improvment project<BR>and I actually have some info about what has been done
so far and what<BR>hasn't. It'd be interesting to see if it matches
up.<BR><BR>There are a few places (Guam, Hawaii islands) where they actually
do use<BR>an SRID other than 4269, but my scripts don't yet handle that and
I'm<BR>guessing that's not what you're referring to anyway. :)<BR><BR>
Thanks!<BR><BR>
Stephen<BR><BR>> On Thu, 2008-04-03 at 17:07 -0400, Stephen
Frost wrote:<BR>> > * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> >
> I think they may have also upgraded their pipe.. I got about
1.41MB/s<BR>> > > (11 Mb/s) for the whole transfer. It's about
22G all told. I'll<BR>> > > probably be trying to load it up
into PG on one of our servers tomorrow.<BR>> > > It was a bit over 4
hours for me to pull down off of their<BR>> > > <A
href="http://ftp2.census.gov" target=_blank>ftp2.census.gov</A> ftp
site.<BR>> ><BR>> > Just to update those who might be interested-
I've finished the data<BR>> > load into one of our servers at work.
It comes to ~60GB on disk in<BR>> > PostgreSQL/PostGIS with
appropriate indexes in most places and whatnot.<BR>> > Based on what
I've seen so far, it looks *very* nice, especially the<BR>> >
hydrogrophy ("areawater"). It also appears to be pretty
consistant<BR>> > across the layers, which is also good.<BR>>
><BR>> > If anyone's interested in the scripts used to load the data
(they're<BR>> > pretty simple, really), I'd be happy to provide
them.<BR>> ><BR>> > Enjoy,<BR>> ><BR>> >
Stephen<BR>> >
_______________________________________________<BR>> > postgis-users
mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/d5bf23c9/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/d5bf23c9/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
16<BR>Date: Thu, 3 Apr 2008 19:37:36 -0400<BR>From: "Paragon Corporation"
<<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject: RE:
[postgis-users] TIGER/Line Shapefiles released<BR>To: "'PostGIS Users
Discussion'"<BR> <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<006601c895e3$b26a87e0$4d812e40@l><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>As a side note to what you were saying
in the .sh file it would be really<BR>nice if the shp2pgsql dealt with
standalone dbf files too. I mean the logic<BR>is all there so doesn't
seem like it would be that hard to put in a switch<BR>for that.<BR><BR>For the
dbfs I was using Ogr2Ogr which works well except it adds a useless<BR>geometry
field.<BR><BR><BR><BR>-----Original Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Stephen<BR>Frost<BR>Sent: Thursday, April 03, 2008 7:16 PM<BR>To:
PostGIS Users Discussion<BR>Subject: Re: [postgis-users] TIGER/Line Shapefiles
released<BR><BR>* Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> If
anyone's interested in the scripts used to load the data (they're<BR>>
pretty simple, really), I'd be happy to provide them.<BR><BR>Alright, since
it's apparently kind of popular, I went ahead and put the<BR>script up
here:<BR><BR><A href="http://snowman.net/tiger/"
target=_blank>http://snowman.net/tiger/</A><BR><BR>I also included a
README.txt that is essentially what I wrote up to the<BR>first person who
asked me for the script. Please let me know if you improve<BR>upon it or
find problems with it.<BR><BR> Thanks,<BR><BR>
Stephen<BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
17<BR>Date: Fri, 04 Apr 2008 01:07:03 +0100<BR>From: "Jonathan W. Lowe" <<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Cc: PostGIS
Users Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<1207267623.4932.38.camel@localhost.localdomain><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>Stephen,<BR><BR>My initial testing has
been on Alameda County (California) TIGER data.<BR>The two attached image
files show an overlay of US Census 2000 Blocks<BR>over an area south of the UC
Berkeley campus. The offset is the same<BR>for both Google and
OpenStreetMap (OSM). This suggests that I've made a<BR>mistake
somewhere, because the OSM tiles in the United States are all<BR>rendered from
TIGER linework, so the TIGER census blocks should match<BR>exactly.<BR><BR>For
the same source shapefile (tabblock00.shp), there's a nearly perfect<BR>match
between block boundaries and streets in the area just South of<BR>Oakland's
Lake Merritt. It smells like a datum conversion issue...<BR><BR>The
conversion path was from shapefile to PostGIS using shp2pgsql. I<BR>used
a custom projection of 32767 rather than 4269 because the existing<BR>srtext
for 4269 had a degree value as 0.01745329251994328, but the US<BR>Census
metadata listed a degree value of 0.017453292519943295. Perhaps<BR>not
significant? My spatial_ref_sys entries for 4269 and 32767
are<BR>otherwise pretty similar:<BR><BR>SRID: 4269<BR>SRTEXT:
GEOGCS["NAD83",DATUM["North_American_Datum_1983",<BR>
SPHEROID["GRS 1980",6378137,298.257222101,<BR>
AUTHORITY["EPSG","7019"]],<BR>
AUTHORITY["EPSG","6269"]],<BR>
PRIMEM["Greenwich",0,<BR>
AUTHORITY["EPSG","8901"]],<BR>
UNIT["degree",0.01745329251994328,<BR>
AUTHORITY["EPSG","9122"]],<BR>
AUTHORITY["EPSG","4269"]]<BR>PROJ4TEXT:
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs<BR><BR>SRID: 32767<BR>SRTEXT:
GEOGCS["GCS_North_American_1983",<BR>
DATUM["D_North_American_1983",<BR>
SPHEROID["GRS_1980",6378137,298.257222101]],<BR>
PRIMEM["Greenwich",0],<BR>
UNIT["Degree",0.017453292519943295]]<BR>PROJ4TEXT: +proj=longlat
+ellps=clrk66 +datum=NAD27 +no_defs<BR><BR>To display census block data in
OpenStreetMap, I extract it from PostGIS<BR>with a transform to EPSG 4326,
although the coordinates don't seem to<BR>change as a result. (This
seems correct, as datum=NAD83 and datum=WGS84<BR>are, for my purposes at
least, are essentially identical.)<BR><BR>Thanks,<BR>Jonathan<BR><BR>2
attachments: TIGER2007andOSM.png, TIGER2007andGoogle.png<BR><BR><BR>On
Thu, 2008-04-03 at 19:18 -0400, Stephen Frost wrote:<BR>>
Jonathan,<BR>><BR>> * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
> Have you yet tried overlaying TIGER 2007 linework or census
block/tract<BR>> > polygons over Google or OpenStreetMap tiles?
I'm seeing a good match in<BR>> > some areas but a significant
shift (~50 meters) in others. Thought it<BR>> > might be a datum
conversion issue, but can't seem to find a match.<BR>><BR>> I hadn't
looked at the linework too much yet or tried to overlay it.<BR>> I'm
curious where you're seeing the differences though because I know<BR>> that
Census is only about half way through their MAF improvment project<BR>> and
I actually have some info about what has been done so far and what<BR>>
hasn't. It'd be interesting to see if it matches up.<BR>><BR>>
There are a few places (Guam, Hawaii islands) where they actually do
use<BR>> an SRID other than 4269, but my scripts don't yet handle that and
I'm<BR>> guessing that's not what you're referring to anyway.
:)<BR>><BR>> Thanks!<BR>><BR>>
Stephen<BR>><BR>> > On Thu,
2008-04-03 at 17:07 -0400, Stephen Frost wrote:<BR>> > > * Stephen
Frost (<A href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>> > > > I think they may have also upgraded their pipe..
I got about 1.41MB/s<BR>> > > > (11 Mb/s) for the whole
transfer. It's about 22G all told. I'll<BR>> > > >
probably be trying to load it up into PG on one of our servers
tomorrow.<BR>> > > > It was a bit over 4 hours for me to pull down
off of their<BR>> > > > <A href="http://ftp2.census.gov"
target=_blank>ftp2.census.gov</A> ftp site.<BR>> > ><BR>> >
> Just to update those who might be interested- I've finished the
data<BR>> > > load into one of our servers at work. It comes to
~60GB on disk in<BR>> > > PostgreSQL/PostGIS with appropriate indexes
in most places and whatnot.<BR>> > > Based on what I've seen so far,
it looks *very* nice, especially the<BR>> > > hydrogrophy
("areawater"). It also appears to be pretty consistant<BR>> > >
across the layers, which is also good.<BR>> > ><BR>> > > If
anyone's interested in the scripts used to load the data (they're<BR>> >
> pretty simple, really), I'd be happy to provide them.<BR>> >
><BR>> > > Enjoy,<BR>> > ><BR>> > >
Stephen<BR>> > >
_______________________________________________<BR>> > >
postgis-users mailing list<BR>> > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> > _______________________________________________<BR>> >
postgis-users mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name:
TIGER2007andGoogle.png<BR>Type: image/png<BR>Size: 123343 bytes<BR>Desc: not
available<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/5c180b3f/TIGER2007andGoogle-0001.png"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/5c180b3f/TIGER2007andGoogle-0001.png</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name:
TIGER2007andOSM.png<BR>Type: image/png<BR>Size: 252886 bytes<BR>Desc: not
available<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/5c180b3f/TIGER2007andOSM-0001.png"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/5c180b3f/TIGER2007andOSM-0001.png</A><BR><BR>------------------------------<BR><BR>Message:
18<BR>Date: Thu, 03 Apr 2008 17:08:29 -0700<BR>From: Ron M <<A
href="mailto:rm_postgis@cheapcomplexdevices.com">rm_postgis@cheapcomplexdevices.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: "Jonathan W. Lowe"
<<A href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>>,
PostGIS Users<BR> Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F5717D.5080301@cheapcomplexdevices.com">47F5717D.5080301@cheapcomplexdevices.com</A>><BR>Content-Type:
text/plain; charset=ISO-8859-1; format=flowed<BR><BR><BR>I'm curious if
they've fixed some of the self-inconsistencies they<BR>seem to have had (or
did I just load the data wrong) in the 2006 (or<BR>was it 2005) data I loaded
earlier.<BR><BR>An example is Interstate 280's odd gap behind
Stanford.<BR><BR><A
href="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"
target=_blank>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</A><BR><BR>Other
examples were the Dumbarton and San Mateo bridges across<BR>San Francisco Bay
not quite connecting with each other; but I<BR>can't provide a link for that
because IIRC we hand-edited those.<BR><BR><BR>Stephen Frost wrote:<BR>>
Jonathan,<BR>><BR>> * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>)
wrote:<BR>>> Have you yet tried overlaying TIGER 2007 linework or census
block/tract<BR>>> polygons over Google or OpenStreetMap tiles? I'm
seeing a good match in<BR>>> some areas but a significant shift (~50
meters) in others. Thought it<BR>>> might be a datum conversion
issue, but can't seem to find a match.<BR>><BR>> I hadn't looked at the
linework too much yet or tried to overlay it.<BR>> I'm curious where you're
seeing the differences though because I know<BR>> that Census is only about
half way through their MAF improvment project<BR>> and I actually have some
info about what has been done so far and what<BR>> hasn't. It'd be
interesting to see if it matches up.<BR>><BR>> There are a few places
(Guam, Hawaii islands) where they actually do use<BR>> an SRID other than
4269, but my scripts don't yet handle that and I'm<BR>> guessing that's not
what you're referring to anyway. :)<BR>><BR>>
Thanks!<BR>><BR>>
Stephen<BR>><BR>>> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost
wrote:<BR>>>> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>>>>> I think they may have also upgraded their pipe..
I got about 1.41MB/s<BR>>>>> (11 Mb/s) for the whole
transfer. It's about 22G all told. I'll<BR>>>>>
probably be trying to load it up into PG on one of our servers
tomorrow.<BR>>>>> It was a bit over 4 hours for me to pull down
off of their<BR>>>>> <A href="http://ftp2.census.gov"
target=_blank>ftp2.census.gov</A> ftp site.<BR>>>> Just to update
those who might be interested- I've finished the data<BR>>>> load
into one of our servers at work. It comes to ~60GB on disk
in<BR>>>> PostgreSQL/PostGIS with appropriate indexes in most places
and whatnot.<BR>>>> Based on what I've seen so far, it looks *very*
nice, especially the<BR>>>> hydrogrophy ("areawater"). It also
appears to be pretty consistant<BR>>>> across the layers, which is
also good.<BR>>>><BR>>>> If anyone's interested in the
scripts used to load the data (they're<BR>>>> pretty simple, really),
I'd be happy to provide them.<BR>>>><BR>>>>
Enjoy,<BR>>>><BR>>>>
Stephen<BR>>>>
_______________________________________________<BR>>>> postgis-users
mailing list<BR>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>><BR>>>
------------------------------------------------------------------------<BR>>><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
19<BR>Date: Fri, 04 Apr 2008 01:27:48 +0100<BR>From: "Jonathan W. Lowe" <<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<1207268868.4932.43.camel@localhost.localdomain><BR>Content-Type:
text/plain<BR><BR>...And in case the images don't persist through the mail
server, they're<BR>viewable at: <A
href="http://www.giswebsite.com/demos/tiger_overlays.html"
target=_blank>http://www.giswebsite.com/demos/tiger_overlays.html</A><BR><BR>On
Fri, 2008-04-04 at 01:07 +0100, Jonathan W. Lowe wrote:<BR>>
Stephen,<BR>><BR>> My initial testing has been on Alameda County
(California) TIGER data.<BR>> The two attached image files show an overlay
of US Census 2000 Blocks<BR>> over an area south of the UC Berkeley campus.
The offset is the same<BR>> for both Google and OpenStreetMap (OSM).
This suggests that I've made a<BR>> mistake somewhere, because the
OSM tiles in the United States are all<BR>> rendered from TIGER linework,
so the TIGER census blocks should match<BR>> exactly.<BR>><BR>> For
the same source shapefile (tabblock00.shp), there's a nearly perfect<BR>>
match between block boundaries and streets in the area just South of<BR>>
Oakland's Lake Merritt. It smells like a datum conversion
issue...<BR>><BR>> The conversion path was from shapefile to PostGIS
using shp2pgsql. I<BR>> used a custom projection of 32767 rather than
4269 because the existing<BR>> srtext for 4269 had a degree value as
0.01745329251994328, but the US<BR>> Census metadata listed a degree value
of 0.017453292519943295. Perhaps<BR>> not significant? My
spatial_ref_sys entries for 4269 and 32767 are<BR>> otherwise pretty
similar:<BR>><BR>> SRID: 4269<BR>> SRTEXT:
GEOGCS["NAD83",DATUM["North_American_Datum_1983",<BR>>
SPHEROID["GRS 1980",6378137,298.257222101,<BR>>
AUTHORITY["EPSG","7019"]],<BR>>
AUTHORITY["EPSG","6269"]],<BR>>
PRIMEM["Greenwich",0,<BR>>
AUTHORITY["EPSG","8901"]],<BR>>
UNIT["degree",0.01745329251994328,<BR>>
AUTHORITY["EPSG","9122"]],<BR>>
AUTHORITY["EPSG","4269"]]<BR>> PROJ4TEXT:
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs<BR>><BR>> SRID:
32767<BR>> SRTEXT: GEOGCS["GCS_North_American_1983",<BR>>
DATUM["D_North_American_1983",<BR>>
SPHEROID["GRS_1980",6378137,298.257222101]],<BR>>
PRIMEM["Greenwich",0],<BR>>
UNIT["Degree",0.017453292519943295]]<BR>> PROJ4TEXT: +proj=longlat
+ellps=clrk66 +datum=NAD27 +no_defs<BR>><BR>> To display census block
data in OpenStreetMap, I extract it from PostGIS<BR>> with a transform to
EPSG 4326, although the coordinates don't seem to<BR>> change as a result.
(This seems correct, as datum=NAD83 and datum=WGS84<BR>> are, for my
purposes at least, are essentially identical.)<BR>><BR>> Thanks,<BR>>
Jonathan<BR>><BR>> 2 attachments: TIGER2007andOSM.png,
TIGER2007andGoogle.png<BR>><BR>><BR>> On Thu, 2008-04-03 at 19:18
-0400, Stephen Frost wrote:<BR>> > Jonathan,<BR>> ><BR>> > *
Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
> > Have you yet tried overlaying TIGER 2007 linework or census
block/tract<BR>> > > polygons over Google or OpenStreetMap tiles?
I'm seeing a good match in<BR>> > > some areas but a
significant shift (~50 meters) in others. Thought it<BR>> > >
might be a datum conversion issue, but can't seem to find a match.<BR>>
><BR>> > I hadn't looked at the linework too much yet or tried to
overlay it.<BR>> > I'm curious where you're seeing the differences
though because I know<BR>> > that Census is only about half way through
their MAF improvment project<BR>> > and I actually have some info about
what has been done so far and what<BR>> > hasn't. It'd be
interesting to see if it matches up.<BR>> ><BR>> > There are a few
places (Guam, Hawaii islands) where they actually do use<BR>> > an SRID
other than 4269, but my scripts don't yet handle that and I'm<BR>> >
guessing that's not what you're referring to anyway. :)<BR>> ><BR>>
> Thanks!<BR>> ><BR>> >
Stephen<BR>> ><BR>> > > On Thu, 2008-04-03
at 17:07 -0400, Stephen Frost wrote:<BR>> > > > * Stephen Frost
(<A href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>>
> > > > I think they may have also upgraded their pipe.. I
got about 1.41MB/s<BR>> > > > > (11 Mb/s) for the whole
transfer. It's about 22G all told. I'll<BR>> > > >
> probably be trying to load it up into PG on one of our servers
tomorrow.<BR>> > > > > It was a bit over 4 hours for me to pull
down off of their<BR>> > > > > <A href="http://ftp2.census.gov"
target=_blank>ftp2.census.gov</A> ftp site.<BR>> > > ><BR>>
> > > Just to update those who might be interested- I've finished the
data<BR>> > > > load into one of our servers at work. It
comes to ~60GB on disk in<BR>> > > > PostgreSQL/PostGIS with
appropriate indexes in most places and whatnot.<BR>> > > > Based
on what I've seen so far, it looks *very* nice, especially the<BR>> >
> > hydrogrophy ("areawater"). It also appears to be pretty
consistant<BR>> > > > across the layers, which is also
good.<BR>> > > ><BR>> > > > If anyone's interested in
the scripts used to load the data (they're<BR>> > > > pretty
simple, really), I'd be happy to provide them.<BR>> > > ><BR>>
> > > Enjoy,<BR>> > >
><BR>> > > >
Stephen<BR>> > > >
_______________________________________________<BR>> > > >
postgis-users mailing list<BR>> > > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> ><BR>> > >
_______________________________________________<BR>> > >
postgis-users mailing list<BR>> > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
20<BR>Date: Thu, 3 Apr 2008 20:31:32 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: "Jonathan W. Lowe"
<<A href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>>,
PostGIS Users<BR> Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080404003132.GA4999@tamriel.snowman.net">20080404003132.GA4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>Jonathan,<BR><BR>* Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
...And in case the images don't persist through the mail server,
they're<BR>> viewable at: <A
href="http://www.giswebsite.com/demos/tiger_overlays.html"
target=_blank>http://www.giswebsite.com/demos/tiger_overlays.html</A><BR><BR>You
know, I just realized that you were talking about the Census 2000<BR>blocks
(tabblock00.shp). Is there some reason you're using that<BR>instead of
the current data (tabblock.shp)? They might not want to<BR>update the
data from 2000 for historical reasons...<BR>(Note: I havn't actually gone and
looked, it just occured to me..)<BR><BR>
Thanks,<BR><BR>
Stephen<BR><BR>> On Fri, 2008-04-04 at 01:07 +0100, Jonathan W. Lowe
wrote:<BR>> > Stephen,<BR>> ><BR>> > My initial testing has
been on Alameda County (California) TIGER data.<BR>> > The two attached
image files show an overlay of US Census 2000 Blocks<BR>> > over an area
south of the UC Berkeley campus. The offset is the same<BR>> > for
both Google and OpenStreetMap (OSM). This suggests that I've made
a<BR>> > mistake somewhere, because the OSM tiles in the United States
are all<BR>> > rendered from TIGER linework, so the TIGER census blocks
should match<BR>> > exactly.<BR>> ><BR>> > For the same
source shapefile (tabblock00.shp), there's a nearly perfect<BR>> > match
between block boundaries and streets in the area just South of<BR>> >
Oakland's Lake Merritt. It smells like a datum conversion
issue...<BR>> ><BR>> > The conversion path was from shapefile to
PostGIS using shp2pgsql. I<BR>> > used a custom projection of
32767 rather than 4269 because the existing<BR>> > srtext for 4269 had a
degree value as 0.01745329251994328, but the US<BR>> > Census metadata
listed a degree value of 0.017453292519943295. Perhaps<BR>> > not
significant? My spatial_ref_sys entries for 4269 and 32767 are<BR>>
> otherwise pretty similar:<BR>> ><BR>> > SRID: 4269<BR>>
> SRTEXT: GEOGCS["NAD83",DATUM["North_American_Datum_1983",<BR>> >
SPHEROID["GRS 1980",6378137,298.257222101,<BR>> >
AUTHORITY["EPSG","7019"]],<BR>> >
AUTHORITY["EPSG","6269"]],<BR>>
> PRIMEM["Greenwich",0,<BR>> >
AUTHORITY["EPSG","8901"]],<BR>> >
UNIT["degree",0.01745329251994328,<BR>> >
AUTHORITY["EPSG","9122"]],<BR>> >
AUTHORITY["EPSG","4269"]]<BR>> > PROJ4TEXT:
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs<BR>> ><BR>> >
SRID: 32767<BR>> > SRTEXT: GEOGCS["GCS_North_American_1983",<BR>>
> DATUM["D_North_American_1983",<BR>> >
SPHEROID["GRS_1980",6378137,298.257222101]],<BR>> >
PRIMEM["Greenwich",0],<BR>> >
UNIT["Degree",0.017453292519943295]]<BR>> > PROJ4TEXT: +proj=longlat
+ellps=clrk66 +datum=NAD27 +no_defs<BR>> ><BR>> > To display
census block data in OpenStreetMap, I extract it from PostGIS<BR>> >
with a transform to EPSG 4326, although the coordinates don't seem to<BR>>
> change as a result. (This seems correct, as datum=NAD83 and
datum=WGS84<BR>> > are, for my purposes at least, are essentially
identical.)<BR>> ><BR>> > Thanks,<BR>> > Jonathan<BR>>
><BR>> > 2 attachments: TIGER2007andOSM.png,
TIGER2007andGoogle.png<BR>> ><BR>> ><BR>> > On Thu,
2008-04-03 at 19:18 -0400, Stephen Frost wrote:<BR>> > >
Jonathan,<BR>> > ><BR>> > > * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
> > > Have you yet tried overlaying TIGER 2007 linework or census
block/tract<BR>> > > > polygons over Google or OpenStreetMap
tiles? I'm seeing a good match in<BR>> > > > some areas but
a significant shift (~50 meters) in others. Thought it<BR>> > >
> might be a datum conversion issue, but can't seem to find a
match.<BR>> > ><BR>> > > I hadn't looked at the linework too
much yet or tried to overlay it.<BR>> > > I'm curious where you're
seeing the differences though because I know<BR>> > > that Census is
only about half way through their MAF improvment project<BR>> > > and
I actually have some info about what has been done so far and what<BR>>
> > hasn't. It'd be interesting to see if it matches up.<BR>>
> ><BR>> > > There are a few places (Guam, Hawaii islands)
where they actually do use<BR>> > > an SRID other than 4269, but my
scripts don't yet handle that and I'm<BR>> > > guessing that's not
what you're referring to anyway. :)<BR>> > ><BR>> > >
Thanks!<BR>> > ><BR>> > >
Stephen<BR>> > ><BR>> > > > On Thu, 2008-04-03 at 17:07
-0400, Stephen Frost wrote:<BR>> > > > > * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> >
> > > > I think they may have also upgraded their pipe.. I
got about 1.41MB/s<BR>> > > > > > (11 Mb/s) for the whole
transfer. It's about 22G all told. I'll<BR>> > > >
> > probably be trying to load it up into PG on one of our servers
tomorrow.<BR>> > > > > > It was a bit over 4 hours for me to
pull down off of their<BR>> > > > > > <A
href="http://ftp2.census.gov" target=_blank>ftp2.census.gov</A> ftp
site.<BR>> > > > ><BR>> > > > > Just to update
those who might be interested- I've finished the data<BR>> > > >
> load into one of our servers at work. It comes to ~60GB on disk
in<BR>> > > > > PostgreSQL/PostGIS with appropriate indexes in
most places and whatnot.<BR>> > > > > Based on what I've seen
so far, it looks *very* nice, especially the<BR>> > > > >
hydrogrophy ("areawater"). It also appears to be pretty
consistant<BR>> > > > > across the layers, which is also
good.<BR>> > > > ><BR>> > > > > If anyone's
interested in the scripts used to load the data (they're<BR>> > >
> > pretty simple, really), I'd be happy to provide them.<BR>> >
> > ><BR>> > > > > Enjoy,<BR>>
> > > ><BR>> > > > >
Stephen<BR>> > > > >
_______________________________________________<BR>> > > > >
postgis-users mailing list<BR>> > > > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> > ><BR>> > > >
_______________________________________________<BR>> > > >
postgis-users mailing list<BR>> > > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/dac89b9a/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/dac89b9a/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
21<BR>Date: Thu, 03 Apr 2008 20:48:49 -0500<BR>From: Stephen Woodbridge <<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: "Jonathan W. Lowe"
<<A href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>>,
PostGIS Users<BR> Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F58901.3050701@swoodbridge.com">47F58901.3050701@swoodbridge.com</A>><BR>Content-Type:
text/plain; charset=UTF-8; format=flowed<BR><BR>Stephen Frost wrote:<BR>>
Jonathan,<BR>><BR>> * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>)
wrote:<BR>>> Have you yet tried overlaying TIGER 2007 linework or census
block/tract<BR>>> polygons over Google or OpenStreetMap tiles? I'm
seeing a good match in<BR>>> some areas but a significant shift (~50
meters) in others. Thought it<BR>>> might be a datum conversion
issue, but can't seem to find a match.<BR>><BR>> I hadn't looked at the
linework too much yet or tried to overlay it.<BR>> I'm curious where you're
seeing the differences though because I know<BR>> that Census is only about
half way through their MAF improvment project<BR>> and I actually have some
info about what has been done so far and what<BR>> hasn't. It'd be
interesting to see if it matches up.<BR>><BR>> There are a few places
(Guam, Hawaii islands) where they actually do use<BR>> an SRID other than
4269, but my scripts don't yet handle that and I'm<BR>> guessing that's not
what you're referring to anyway. :)<BR><BR>I had an extensive discussion with
some of the Census Geography staff<BR>about the Island provinces and Hawaii
because I was trying to align<BR>Navteq routes over Tiger data and the Navteq
routes were about a .25-.5<BR>miles east of the Tiger data in
Hawaii.<BR><BR>It turns out that they really have not idea what the "local
datums" are<BR>that were originally used. I tried a lot of the local Island
datums in<BR>the proj4 epsg file but could not find any close
matches.<BR><BR>It will be nice when they get that fixed up
:)<BR><BR>Stephen - thank you for sharing your scripts.<BR><BR>Best
regards,<BR> -Stephen Woodbridge<BR> <A
href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR><BR>>
Thanks!<BR>><BR>>
Stephen<BR>><BR>>> On Thu, 2008-04-03 at 17:07 -0400, Stephen Frost
wrote:<BR>>>> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>>>>> I think they may have also upgraded their pipe..
I got about 1.41MB/s<BR>>>>> (11 Mb/s) for the whole
transfer. It's about 22G all told. I'll<BR>>>>>
probably be trying to load it up into PG on one of our servers
tomorrow.<BR>>>>> It was a bit over 4 hours for me to pull down
off of their<BR>>>>> <A href="http://ftp2.census.gov"
target=_blank>ftp2.census.gov</A> ftp site.<BR>>>> Just to update
those who might be interested- I've finished the data<BR>>>> load
into one of our servers at work. It comes to ~60GB on disk
in<BR>>>> PostgreSQL/PostGIS with appropriate indexes in most places
and whatnot.<BR>>>> Based on what I've seen so far, it looks *very*
nice, especially the<BR>>>> hydrogrophy ("areawater"). It also
appears to be pretty consistant<BR>>>> across the layers, which is
also good.<BR>>>><BR>>>> If anyone's interested in the
scripts used to load the data (they're<BR>>>> pretty simple, really),
I'd be happy to provide them.<BR>>>><BR>>>>
Enjoy,<BR>>>><BR>>>>
Stephen<BR>>>>
_______________________________________________<BR>>>> postgis-users
mailing list<BR>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>><BR>>>
------------------------------------------------------------------------<BR>>><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
22<BR>Date: Thu, 03 Apr 2008 20:52:50 -0500<BR>From: Stephen Woodbridge <<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F589F2.4060804@swoodbridge.com">47F589F2.4060804@swoodbridge.com</A>><BR>Content-Type:
text/plain; charset=ISO-8859-1; format=flowed<BR><BR>Yes, it would be ideal if
it was handled by shp2pgsql but have you<BR>looked at:<BR><BR><A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search</A><BR><A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search</A><BR><A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search</A><BR><BR>some
of these might be helpful.<BR><BR>Best regards,<BR> -Stephen
Woodbridge<BR> <A href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR><BR>Paragon Corporation
wrote:<BR>> As a side note to what you were saying in the .sh file it would
be really<BR>> nice if the shp2pgsql dealt with standalone dbf files too.
I mean the logic<BR>> is all there so doesn't seem like it would be
that hard to put in a switch<BR>> for that.<BR>><BR>> For the dbfs I
was using Ogr2Ogr which works well except it adds a useless<BR>> geometry
field.<BR>><BR>><BR>><BR>> -----Original Message-----<BR>>
From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>
[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Stephen<BR>> Frost<BR>> Sent: Thursday, April 03, 2008 7:16
PM<BR>> To: PostGIS Users Discussion<BR>> Subject: Re: [postgis-users]
TIGER/Line Shapefiles released<BR>><BR>> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>>> If
anyone's interested in the scripts used to load the data (they're<BR>>>
pretty simple, really), I'd be happy to provide them.<BR>><BR>> Alright,
since it's apparently kind of popular, I went ahead and put the<BR>> script
up here:<BR>><BR>> <A href="http://snowman.net/tiger/"
target=_blank>http://snowman.net/tiger/</A><BR>><BR>> I also included a
README.txt that is essentially what I wrote up to the<BR>> first person who
asked me for the script. Please let me know if you improve<BR>> upon
it or find problems with it.<BR>><BR>>
Thanks,<BR>><BR>>
Stephen<BR>><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
23<BR>Date: Thu, 3 Apr 2008 20:59:43 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080404005943.GB4999@tamriel.snowman.net">20080404005943.GB4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>Stephen,<BR><BR>* Stephen Woodbridge (<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>)
wrote:<BR>> I had an extensive discussion with some of the Census Geography
staff<BR>> about the Island provinces and Hawaii because I was trying to
align<BR>> Navteq routes over Tiger data and the Navteq routes were about a
.25-.5<BR>> miles east of the Tiger data in
Hawaii.<BR><BR>Interesting...<BR><BR>> It turns out that they really have
not idea what the "local datums" are<BR>> that were originally used. I
tried a lot of the local Island datums in<BR>> the proj4 epsg file but
could not find any close matches.<BR><BR>In their documentation they
talk about the datums used for these<BR>regions.. Lemme go look up what
they say..<BR><BR>Here we go:<BR><BR>North American Datum of 1983 in the 48
contiguous states, the District<BR>of Columbia, Alaska, Hawaii (only Oahu
Island within Honolulu County, HI<BR>(15003)), Puerto Rico, and the U.S.
Virgin Islands.<BR><BR>Regional datums are used in the Pacific Island Areas
(American Samoa,<BR>Guam, and the Commonwealth of the Northern Mariana
Islands) and most of<BR>Hawaii.<BR><BR>The datums used in the remainder of
Hawaii are as follows: Hawaii<BR>County, HI (15001), Old Hawaiian Datum;
Honolulu County, HI (15003),<BR>local astronomic datums for all islands
northwest of 161 degrees west<BR>longitude; Kalawao County, HI (15005), Old
Hawaiian Datum; Kauai County,<BR>HI (15007), Old Hawaiian Datum for Kauai
Island and local astronomic<BR>datum for Kaula Rock; Maui County, HI (15009),
Old Hawaiian Datum.<BR><BR>The datums used in American Samoa are as follows:
Eastern District, AS<BR>(60010), American Samoa Datum of 1962; Manu'a
District, AS (60020),<BR>American Samoa Datum of 1962; Rose Island, AS
(60030), local astronomic<BR>datum; Swains Island, AS (60040), local
astronomic datum 1939; Western<BR>District, AS (60050), American Samoa Datum
of 1962.<BR><BR>The Guam Datum of 1963 is used in Guam (66010). The
datums used in the<BR>Commonwealth of the Northern Mariana Islands are as
follows: Northern<BR>Islands Municipality (69085), Guam Datum of 1963
(Agrihan, Alamagan,<BR>Anatahan, Gugan, Medinilla, Pagan, and Sarigan Islands)
and local<BR>astronomic datums (Asuncion, Maug, and Farallon De Pajaros
Islands);<BR>Rota Municipality (69100), Guam Datum of 1963; Saipan
Municipality<BR>(69110), Guam Datum of 1963; Tinian Municipality (69120), Guam
Datum of<BR>1963.<BR><BR>So, there's somewhat more information there than
perhaps there was<BR>previously, but I havn't gone hunting for, eg, "Old
Hawaiian Datum". If<BR>someone does come up with the correct
datums/srids/etc based off of this<BR>(or whatever), I'd be happy to update my
script accordingly.<BR><BR> Thanks!<BR><BR>
Stephen<BR><BR>> It will be
nice when they get that fixed up :)<BR>><BR>> Stephen - thank you for
sharing your scripts.<BR>><BR>> Best regards,<BR>> -Stephen
Woodbridge<BR>> <A href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR>><BR>>>
Thanks!<BR>>><BR>>>
Stephen<BR>>><BR>>>> On Thu, 2008-04-03 at 17:07
-0400, Stephen Frost wrote:<BR>>>>> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>>>>>> I think they may have also upgraded their
pipe.. I got about 1.41MB/s<BR>>>>>> (11 Mb/s) for the
whole transfer. It's about 22G all told.
I'll<BR>>>>>> probably be trying to load it up into PG on
one of our servers tomorrow.<BR>>>>>> It was a bit over 4 hours
for me to pull down off of their<BR>>>>>> <A
href="http://ftp2.census.gov" target=_blank>ftp2.census.gov</A> ftp
site.<BR>>>>> Just to update those who might be interested- I've
finished the data<BR>>>>> load into one of our servers at work.
It comes to ~60GB on disk in<BR>>>>> PostgreSQL/PostGIS with
appropriate indexes in most places and whatnot.<BR>>>>> Based on
what I've seen so far, it looks *very* nice, especially
the<BR>>>>> hydrogrophy ("areawater"). It also appears to be
pretty consistant<BR>>>>> across the layers, which is also
good.<BR>>>>><BR>>>>> If anyone's interested in the
scripts used to load the data (they're<BR>>>>> pretty simple,
really), I'd be happy to provide them.<BR>>>>><BR>>>>>
Enjoy,<BR>>>>><BR>>>>>
Stephen<BR>>>>>
_______________________________________________<BR>>>>>
postgis-users mailing list<BR>>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>>
_______________________________________________<BR>>>> postgis-users
mailing list<BR>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>><BR>>>>
------------------------------------------------------------------------<BR>>>><BR>>>>
_______________________________________________<BR>>>> postgis-users
mailing list<BR>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/cd560eac/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/cd560eac/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
24<BR>Date: Thu, 3 Apr 2008 21:01:09 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080404010108.GC4999@tamriel.snowman.net">20080404010108.GC4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>* Stephen Woodbridge (<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>)
wrote:<BR>> Yes, it would be ideal if it was handled by shp2pgsql but have
you<BR>> looked at:<BR>><BR>> <A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search</A><BR>>
<A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search</A><BR>>
<A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search</A><BR>><BR>>
some of these might be helpful.<BR><BR>Just fyi, my script uses dbview from
the, conveniantly named, dbview<BR>Debian package. It works quite well
for me. Not that I'd be against<BR>having that functionality in
shp2pgsql, it's certainly be useful.<BR><BR>First I'd like to see an option to
add the primary key *after* the data<BR>is loaded though.<BR><BR>
Thanks,<BR><BR>
Stephen<BR><BR>> Paragon Corporation wrote:<BR>>> As a side
note to what you were saying in the .sh file it would be really<BR>>>
nice if the shp2pgsql dealt with standalone dbf files too. I mean the
logic<BR>>> is all there so doesn't seem like it would be that hard to
put in a switch<BR>>> for that.<BR>>><BR>>> For the dbfs I
was using Ogr2Ogr which works well except it adds a useless<BR>>>
geometry field.<BR>>><BR>>><BR>>><BR>>> -----Original
Message-----<BR>>> From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>>
[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Stephen<BR>>> Frost<BR>>> Sent: Thursday, April 03,
2008 7:16 PM<BR>>> To: PostGIS Users Discussion<BR>>> Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>>><BR>>> *
Stephen Frost (<A href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>>>> If anyone's interested in the scripts used to load the
data (they're<BR>>>> pretty simple, really), I'd be happy to provide
them.<BR>>><BR>>> Alright, since it's apparently kind of popular,
I went ahead and put the<BR>>> script up here:<BR>>><BR>>>
<A href="http://snowman.net/tiger/"
target=_blank>http://snowman.net/tiger/</A><BR>>><BR>>> I also
included a README.txt that is essentially what I wrote up to the<BR>>>
first person who asked me for the script. Please let me know if you
improve<BR>>> upon it or find problems with it.<BR>>><BR>>>
Thanks,<BR>>><BR>>>
Stephen<BR>>><BR>>><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/366a2d47/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/366a2d47/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
25<BR>Date: Thu, 3 Apr 2008 21:10:17 -0400<BR>From: "Paragon Corporation"
<<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject: RE:
[postgis-users] TIGER/Line Shapefiles released<BR>To: "'PostGIS Users
Discussion'"<BR> <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<00a801c895f0$a57ef630$4d812e40@l><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>Thanks I'll take a look. I was
thinking more for completeness and the<BR>marginal effort that I think would
be involved from my memory of what the<BR>shp2pgsql C code looks like. I would
change it myself, but I'm moderately<BR>afraid of C code.<BR><BR>-----Original
Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Stephen<BR>Woodbridge<BR>Sent: Thursday, April 03, 2008 9:53
PM<BR>To: PostGIS Users Discussion<BR>Subject: Re: [postgis-users] TIGER/Line
Shapefiles released<BR><BR>Yes, it would be ideal if it was handled by
shp2pgsql but have you looked<BR>at:<BR><BR><A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2pgsql&<BR>btnG=Search</A><BR><A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2psql&b<BR>tnG=Search</A><BR><A
href="http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&btnG=Search"
target=_blank>http://www.google.com/search?num=100&hl=en&newwindow=1&safe=off&q=dbf2sql&bt<BR>nG=Search</A><BR><BR>some
of these might be helpful.<BR><BR>Best regards,<BR> -Stephen
Woodbridge<BR> <A href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR><BR>Paragon Corporation
wrote:<BR>> As a side note to what you were saying in the .sh file it would
be<BR>> really nice if the shp2pgsql dealt with standalone dbf files too.
I<BR>> mean the logic is all there so doesn't seem like it would be
that hard<BR>> to put in a switch for that.<BR>><BR>> For the dbfs I
was using Ogr2Ogr which works well except it adds a<BR>> useless geometry
field.<BR>><BR>><BR>><BR>> -----Original Message-----<BR>>
From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>
[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of<BR>> Stephen Frost<BR>> Sent: Thursday, April 03, 2008 7:16
PM<BR>> To: PostGIS Users Discussion<BR>> Subject: Re: [postgis-users]
TIGER/Line Shapefiles released<BR>><BR>> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>>> If
anyone's interested in the scripts used to load the data (they're<BR>>>
pretty simple, really), I'd be happy to provide them.<BR>><BR>> Alright,
since it's apparently kind of popular, I went ahead and put<BR>> the script
up here:<BR>><BR>> <A href="http://snowman.net/tiger/"
target=_blank>http://snowman.net/tiger/</A><BR>><BR>> I also included a
README.txt that is essentially what I wrote up to<BR>> the first person who
asked me for the script. Please let me know if<BR>> you improve upon
it or find problems with it.<BR>><BR>>
Thanks,<BR>><BR>>
Stephen<BR>><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
26<BR>Date: Thu, 03 Apr 2008 21:48:38 -0500<BR>From: Stephen Woodbridge <<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F59706.6090409@swoodbridge.com">47F59706.6090409@swoodbridge.com</A>><BR>Content-Type:
text/plain; charset=UTF-8; format=flowed<BR><BR>Stephen Frost wrote:<BR>>
Stephen,<BR>><BR>> * Stephen Woodbridge (<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>)
wrote:<BR>>> I had an extensive discussion with some of the Census
Geography staff<BR>>> about the Island provinces and Hawaii because I
was trying to align<BR>>> Navteq routes over Tiger data and the Navteq
routes were about a .25-.5<BR>>> miles east of the Tiger data in
Hawaii.<BR>><BR>> Interesting...<BR>><BR>>> It turns out that
they really have not idea what the "local datums" are<BR>>> that were
originally used. I tried a lot of the local Island datums in<BR>>> the
proj4 epsg file but could not find any close matches.<BR>><BR>> In
their documentation they talk about the datums used for these<BR>>
regions.. Lemme go look up what they say..<BR>><BR>> Here we
go:<BR>><BR>> North American Datum of 1983 in the 48 contiguous states,
the District<BR>> of Columbia, Alaska, Hawaii (only Oahu Island within
Honolulu County, HI<BR>> (15003)), Puerto Rico, and the U.S. Virgin
Islands.<BR>><BR>> Regional datums are used in the Pacific Island Areas
(American Samoa,<BR>> Guam, and the Commonwealth of the Northern Mariana
Islands) and most of<BR>> Hawaii.<BR>><BR>> The datums used in the
remainder of Hawaii are as follows: Hawaii<BR>> County, HI (15001),
Old Hawaiian Datum; Honolulu County, HI (15003),<BR>> local astronomic
datums for all islands northwest of 161 degrees west<BR>> longitude;
Kalawao County, HI (15005), Old Hawaiian Datum; Kauai County,<BR>> HI
(15007), Old Hawaiian Datum for Kauai Island and local astronomic<BR>>
datum for Kaula Rock; Maui County, HI (15009), Old Hawaiian
Datum.<BR>><BR>> The datums used in American Samoa are as follows:
Eastern District, AS<BR>> (60010), American Samoa Datum of 1962;
Manu'a District, AS (60020),<BR>> American Samoa Datum of 1962; Rose
Island, AS (60030), local astronomic<BR>> datum; Swains Island, AS (60040),
local astronomic datum 1939; Western<BR>> District, AS (60050), American
Samoa Datum of 1962.<BR>><BR>> The Guam Datum of 1963 is used in Guam
(66010). The datums used in the<BR>> Commonwealth of the Northern
Mariana Islands are as follows: Northern<BR>> Islands Municipality
(69085), Guam Datum of 1963 (Agrihan, Alamagan,<BR>> Anatahan, Gugan,
Medinilla, Pagan, and Sarigan Islands) and local<BR>> astronomic datums
(Asuncion, Maug, and Farallon De Pajaros Islands);<BR>> Rota Municipality
(69100), Guam Datum of 1963; Saipan Municipality<BR>> (69110), Guam Datum
of 1963; Tinian Municipality (69120), Guam Datum of<BR>>
1963.<BR>><BR>> So, there's somewhat more information there than perhaps
there was<BR>> previously, but I havn't gone hunting for, eg, "Old Hawaiian
Datum". If<BR>> someone does come up with the correct
datums/srids/etc based off of this<BR>> (or whatever), I'd be happy to
update my script accordingly.<BR><BR>Yeah, that is pretty much what they
finally offered to me after a lot of<BR>research. I think some of this is
newer info than I originally got. I<BR>would have to go digging for my notes,
and this info about is probably<BR>more accurate at this point.<BR><BR>I was
primarily focused on the problem I had with the route alignments<BR>that we
resolved by using the Navteq data for the maps when we were<BR>displaying
routes.<BR><BR> From proj4 epsg:<BR><BR># Old Hawaiian<BR><4135>
+proj=longlat +ellps=clrk66 +no_defs <><BR><BR># American Samoa
1962<BR><4169> +proj=longlat +ellps=clrk66
+towgs84=-115,118,426,0,0,0,0<BR>+no_defs <><BR><BR> From
proj4 esri<BR><BR># GCS Guam 1963<BR><37220> +proj=longlat +ellps=clrk66
no_defs <><BR><BR>Best regards,<BR> -Stephen
Woodbridge<BR> <A href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR><BR>>
Thanks!<BR>><BR>>
Stephen<BR>><BR>>> It will be nice when they get that fixed up
:)<BR>>><BR>>> Stephen - thank you for sharing your
scripts.<BR>>><BR>>> Best regards,<BR>>> -Stephen
Woodbridge<BR>>> <A href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR>>><BR>>>>
Thanks!<BR>>>><BR>>>>
Stephen<BR>>>><BR>>>>> On Thu, 2008-04-03
at 17:07 -0400, Stephen Frost wrote:<BR>>>>>> * Stephen Frost
(<A href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>>>>>>> I think they may have also upgraded their
pipe.. I got about 1.41MB/s<BR>>>>>>> (11 Mb/s) for
the whole transfer. It's about 22G all told.
I'll<BR>>>>>>> probably be trying to load it up into
PG on one of our servers tomorrow.<BR>>>>>>> It was a bit
over 4 hours for me to pull down off of their<BR>>>>>>> <A
href="http://ftp2.census.gov" target=_blank>ftp2.census.gov</A> ftp
site.<BR>>>>>> Just to update those who might be interested-
I've finished the data<BR>>>>>> load into one of our servers at
work. It comes to ~60GB on disk in<BR>>>>>>
PostgreSQL/PostGIS with appropriate indexes in most places and
whatnot.<BR>>>>>> Based on what I've seen so far, it looks
*very* nice, especially the<BR>>>>>> hydrogrophy ("areawater").
It also appears to be pretty consistant<BR>>>>>> across
the layers, which is also
good.<BR>>>>>><BR>>>>>> If anyone's interested
in the scripts used to load the data (they're<BR>>>>>> pretty
simple, really), I'd be happy to provide
them.<BR>>>>>><BR>>>>>>
Enjoy,<BR>>>>>><BR>>>>>>
Stephen<BR>>>>>>
_______________________________________________<BR>>>>>>
postgis-users mailing list<BR>>>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>>>
_______________________________________________<BR>>>>>
postgis-users mailing list<BR>>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>>><BR>>>>>
------------------------------------------------------------------------<BR>>>>><BR>>>>>
_______________________________________________<BR>>>>>
postgis-users mailing list<BR>>>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>><BR>>>
------------------------------------------------------------------------<BR>>><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
27<BR>Date: Thu, 03 Apr 2008 22:40:18 -0500<BR>From: Stephen Woodbridge <<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F5A322.3050802@swoodbridge.com">47F5A322.3050802@swoodbridge.com</A>><BR>Content-Type:
text/plain; charset=UTF-8; format=flowed<BR><BR>Stephen Frost wrote:<BR>> *
Stephen Frost (<A href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>)
wrote:<BR>>> If anyone's interested in the scripts used to load the data
(they're<BR>>> pretty simple, really), I'd be happy to provide
them.<BR>><BR>> Alright, since it's apparently kind of popular, I went
ahead and put the<BR>> script up here:<BR>><BR>> <A
href="http://snowman.net/tiger/"
target=_blank>http://snowman.net/tiger/</A><BR>><BR>> I also included a
README.txt that is essentially what I wrote up to the<BR>> first person who
asked me for the script. Please let me know if you<BR>> improve upon
it or find problems with it.<BR><BR>Stephen,<BR><BR>I added to
process_tiger.sh:<BR><BR># User to connect to database with or use
USER=$LOGNAME<BR>USER="postgres"<BR><BR>and in vi
did:<BR><BR>%s/\<psql\>/psql -U $USER/g<BR><BR>to update the
script.<BR><BR>Question on the README.txt<BR><BR>-n Load
national-level data<BR>-b Load state-level data<BR>-c
Load county-level data<BR><BR>Do the above options load all the data at
the given level or do I also<BR>need to run it for each state and county with
the options below?<BR><BR>-s <2-digit state code> Individual state
code to load<BR>-c <3-digit or 5-digit county code> Individual
county code to load<BR><BR>Does:<BR><BR>cd
/u/srcdata/tiger2007fe<BR>./process_tiger.sh -n -b -c<BR><BR>load all the
data? Assuming that that directory is a mirror of the<BR>Census download
directory.<BR><BR>Thanks,<BR> -Stephen Woodbridge<BR> <A
href="http://imaptools.com/"
target=_blank>http://imaptools.com/</A><BR><BR><BR>------------------------------<BR><BR>Message:
28<BR>Date: Thu, 3 Apr 2008 22:54:08 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080404025408.GD4999@tamriel.snowman.net">20080404025408.GD4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>Stephen,<BR><BR>* Stephen Woodbridge (<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>)
wrote:<BR>> I added to process_tiger.sh:<BR>><BR>> # User to connect
to database with or use USER=$LOGNAME<BR>> USER="postgres"<BR><BR>Ah, sure,
makes sense. We user Kerberos for our authentication, so I<BR>tend to
forget about user/pw authentication issues.<BR><BR>> and in vi
did:<BR>><BR>> %s/\<psql\>/psql -U $USER/g<BR>><BR>> to
update the script.<BR><BR>All of the options passed to psql really should be
done as options to<BR>the script. I'll look at adding that (and -U
support) sometime<BR>tomorrow.<BR><BR>> Question on the
README.txt<BR>><BR>> -n Load national-level data<BR>>
-b Load state-level data<BR>> -c Load
county-level data<BR>><BR>> Do the above options load all the data at
the given level or do I also<BR>> need to run it for each state and county
with the options below?<BR><BR>Yes, by default it'll load all data at that
level.<BR><BR>> -s <2-digit state code> Individual state code
to load<BR>> -c <3-digit or 5-digit county code> Individual
county code to load<BR><BR>These are used if you, for example, have to reload
a specific state<BR>and/or county. I had the luxury of doing this when
someone decided to<BR>restart the database in the middle of my data load..
:)<BR><BR>> Does:<BR>><BR>> cd /u/srcdata/tiger2007fe<BR>>
./process_tiger.sh -n -b -c<BR>><BR>> load all the data? Assuming that
that directory is a mirror of the<BR>> Census download
directory.<BR><BR>Yes, it should, though it expects to be run one level up
from<BR>'TIGER2007FE', unless you adjust the "BASE" environment variable at
the<BR>top of the script.<BR><BR>
Thanks,<BR><BR>
Stephen<BR>-------------- next part --------------<BR>A non-text
attachment was scrubbed...<BR>Name: not available<BR>Type:
application/pgp-signature<BR>Size: 189 bytes<BR>Desc: Digital signature<BR>Url
: <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/6ba49bb6/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/6ba49bb6/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
29<BR>Date: Fri, 04 Apr 2008 00:05:53 -0500<BR>From: Stephen Woodbridge <<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:47F5B731.2080309@swoodbridge.com">47F5B731.2080309@swoodbridge.com</A>><BR>Content-Type:
text/plain; charset=UTF-8; format=flowed<BR><BR>OK, Now I'm feeling kind of
dumb.<BR><BR>./process_tiger.sh -n<BR>Internal error!<BR>./process_tiger.sh -n
\*<BR>Internal error!<BR>./process_tiger.sh -n ''<BR>Internal
error!<BR><BR>woodbri@carto:~/work/new-tiger$ sh -x process_tiger.sh -d
-n<BR>+ BASE=TIGER2007FE<BR>+ SETBASE=fe_2007<BR>+ PREFIX=tiger<BR>+
SKIP00=y<BR>++ mktemp -d -p .<BR>+ TMPDIR=./tmp.ZKN9vx<BR>+ SRID=4269<BR>+
USER=postgres<BR>+ HOST=carto<BR>+ DB=tiger2007fe<BR>+ ENCODING=LATIN1<BR>+
NATIONAL=false<BR>+ STATELVL=false<BR>+ STATES=<BR>+ COUNTYLVL=false<BR>+
COUNTIES=<BR>+ DROP=false<BR>++ getopt -o n::ls::bc::ed -n process_tiger.sh --
-d -n<BR>+ TEMP= -d -n '' --<BR>+ '[' 0 '!=' 0 ']'<BR>+ eval set -- ' -d -n
'\'''\'' --'<BR>++ set -- -d -n '' --<BR>+ true<BR>+ DROP=true<BR>+ shift<BR>+
true<BR>+ NATIONAL=true<BR>+ shift<BR>+ true<BR>+ echo 'Internal
error!'<BR>Internal error!<BR>+ exit 1<BR><BR>What did I miss
here.<BR><BR>-Steve<BR><BR>Stephen Frost wrote:<BR>>
Stephen,<BR>><BR>> * Stephen Woodbridge (<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>)
wrote:<BR>>> I added to process_tiger.sh:<BR>>><BR>>> # User
to connect to database with or use USER=$LOGNAME<BR>>>
USER="postgres"<BR>><BR>> Ah, sure, makes sense. We user Kerberos
for our authentication, so I<BR>> tend to forget about user/pw
authentication issues.<BR>><BR>>> and in vi
did:<BR>>><BR>>> %s/\<psql\>/psql -U
$USER/g<BR>>><BR>>> to update the script.<BR>><BR>> All of
the options passed to psql really should be done as options to<BR>> the
script. I'll look at adding that (and -U support) sometime<BR>>
tomorrow.<BR>><BR>>> Question on the
README.txt<BR>>><BR>>> -n Load national-level
data<BR>>> -b Load state-level data<BR>>> -c
Load county-level data<BR>>><BR>>> Do the above options
load all the data at the given level or do I also<BR>>> need to run it
for each state and county with the options below?<BR>><BR>> Yes, by
default it'll load all data at that level.<BR>><BR>>> -s <2-digit
state code> Individual state code to load<BR>>> -c <3-digit
or 5-digit county code> Individual county code to
load<BR>><BR>> These are used if you, for example, have to reload a
specific state<BR>> and/or county. I had the luxury of doing this
when someone decided to<BR>> restart the database in the middle of my data
load.. :)<BR>><BR>>> Does:<BR>>><BR>>> cd
/u/srcdata/tiger2007fe<BR>>> ./process_tiger.sh -n -b
-c<BR>>><BR>>> load all the data? Assuming that that directory is
a mirror of the<BR>>> Census download directory.<BR>><BR>> Yes, it
should, though it expects to be run one level up from<BR>> 'TIGER2007FE',
unless you adjust the "BASE" environment variable at the<BR>> top of the
script.<BR>><BR>> Thanks,<BR>><BR>>
Stephen<BR>><BR>><BR>>
------------------------------------------------------------------------<BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
30<BR>Date: Thu, 3 Apr 2008 21:34:47 -0700 (PDT)<BR>From: "<A
href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</A>"
<<A
href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</A>><BR>Subject:
[postgis-users] Cannot display postGIS layers on ms4w 4.6.1<BR>To: <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>Message-ID:
<<A
href="mailto:168108.36541.qm@web56105.mail.re3.yahoo.com">168108.36541.qm@web56105.mail.re3.yahoo.com</A>><BR>Content-Type:
text/plain; charset="iso-8859-1"<BR><BR>I'm trying to display postGIS layers
on MapServer. I<BR>am using the following components:<BR>1) MapServer 4.6.1
for Windows (ms4w)<BR>2) postgreSQL 8.2<BR>3) shp2pgsql release 1.1.6<BR>4)
QuantumGIS 0.9.2 to automatically generate my<BR>mapfile<BR><BR>My mapfile is
attached with this email. It only has a<BR>single layer (for testing purpose),
and it does not<BR>contain any complex query at all. The layer
definition<BR>is as
follows:<BR>=======================================================<BR> LAYER<BR>
NAME 'batas_administrasi_line2_utm'<BR> TYPE LINE<BR>
CONNECTIONTYPE postgis<BR> CONNECTION 'host=localhost
user=rtrw<BR>dbname=rtrw-pacitan-trenggalek'<BR> DATA 'the_geom
FROM<BR>batas-admin.batas_administrasi_line2_utm'<BR>=======================================================<BR><BR><BR>Yet,
when I tried to display the mapfile, I got the<BR>following error
message:<BR>=======================================================<BR>msDrawMap():
Image handling error. Failed to draw<BR>layer named
'batas_administrasi_line2_utm'.<BR>prepare_database(): Query error. Error
executing<BR>POSTGIS DECLARE (the actual query) statement:
'DECLARE<BR>mycursor BINARY CURSOR FOR
SELECT<BR>asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text<BR>from
batas-admin.batas_administrasi_line2_utm WHERE ()<BR>and (the_geom &&
setSRID(
'BOX3D(528656.510416667<BR>9074209,609071.177083333<BR>9134520)'::BOX3D,find_srid('','batas-admin.batas_administrasi_line2_utm','the_geom')<BR>))'<BR><BR>Postgresql
reports the error as 'ERROR: syntax error<BR>at or near "-" at character 116
'<BR><BR>More Help:<BR><BR>Error with POSTGIS data variable. You
specified<BR>'<check your .map file>'.<BR>Standard ways of specifiying
are :<BR>(1) 'geometry_column from geometry_table'<BR>(2) 'geometry_column
from (<sub query>) as foo using<BR>unique <column name> using
SRID=<srid#>'<BR><BR>Make sure you put in the 'using unique <column
name>'<BR>and 'using SRID=#' clauses in.<BR><BR>For more help, please
see<BR><A href="http://postgis.refractions.net/documentation.php"
target=_blank>http://postgis.refractions.net/documentation.php</A><BR><BR>Mappostgis.c
- version of Jan
23/2004.<BR>=======================================================<BR><BR>I
have tried the following solution:<BR><BR>(1) changing "FROM" to "from"
(uppercase to lowercase)<BR><BR>(2) enabling oid in my database:<BR>-
uncommenting the line "default_with_oids" in my<BR>postgresql.on<BR>- changing
the value to "on"<BR>- restarting posgresql<BR>- re-creating all my postGIS
tables<BR><BR><BR>Yet none of the solution above work. MapServer just<BR>keeps
giving me the error message.<BR><BR>What happens? Anyone know a working
solution?<BR><BR>Thanks,<BR>-Kresh<BR><BR><BR><BR><BR>____________________________________________________________________________________<BR>You
rock. That's why Blockbuster's offering you one<BR>month of Blockbuster Total
Access, No Cost.<BR><A
href="http://tc.deals.yahoo.com/tc/blockbuster/text5.com"
target=_blank>http://tc.deals.yahoo.com/tc/blockbuster/text5.com</A><BR><BR>__________________________________________________________________<BR>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.<BR><BR><BR>
____________________________________________________________________________________<BR>You
rock. That's why Blockbuster's offering you one month of Blockbuster Total
Access, No Cost.<BR><A
href="http://tc.deals.yahoo.com/tc/blockbuster/text5.com"
target=_blank>http://tc.deals.yahoo.com/tc/blockbuster/text5.com</A><BR>--------------
next part --------------<BR>A non-text attachment was scrubbed...<BR>Name:
rtrw-pacitan-trenggalek1.map<BR>Type: application/octet-stream<BR>Size: 2994
bytes<BR>Desc: 165038048-rtrw-pacitan-trenggalek1.map<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/38e12258/rtrw-pacitan-trenggalek1.dll"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080403/38e12258/rtrw-pacitan-trenggalek1.dll</A><BR><BR>------------------------------<BR><BR>Message:
31<BR>Date: Fri, 4 Apr 2008 01:37:24 -0400<BR>From: "Paragon Corporation"
<<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject: RE:
[postgis-users] Cannot display postGIS layers on ms4w<BR>
4.6.1<BR>To: "'PostGIS Users Discussion'"<BR>
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<001701c89615$f6453730$50812e40@r><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>Just a guess I think bata-admin as a
schema is a bad choice of schema names.<BR>I think you have to quote it if you
are going to use that. I would change<BR>it to rename your schema if I
were you. To something like bata_admin<BR><BR>If you go with what you
have, I think you may need to quote it and I'm not<BR>even sure if that is
legal in mapserver since I've never needed to quote
my<BR>schemas.<BR><BR> LAYER<BR> NAME
'batas_administrasi_line2_utm'<BR> TYPE LINE<BR>
CONNECTIONTYPE postgis<BR> CONNECTION 'host=localhost
user=rtrw dbname=rtrw-pacitan-trenggalek'<BR> DATA 'the_geom
FROM<BR>"batas-admin".batas_administrasi_line2_utm'<BR><BR>Hope that
helps,<BR>Regina<BR><BR><BR><BR>-----Original Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of<BR><A
href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</A><BR>Sent:
Friday, April 04, 2008 12:35 AM<BR>To: <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>Subject:
[postgis-users] Cannot display postGIS layers on ms4w 4.6.1<BR><BR>I'm trying
to display postGIS layers on MapServer. I am using the
following<BR>components:<BR>1) MapServer 4.6.1 for Windows (ms4w)<BR>2)
postgreSQL 8.2<BR>3) shp2pgsql release 1.1.6<BR>4) QuantumGIS 0.9.2 to
automatically generate my mapfile<BR><BR>My mapfile is attached with this
email. It only has a single layer (for<BR>testing purpose), and it does not
contain any complex query at all. The<BR>layer definition is as
follows:<BR>=======================================================<BR> LAYER<BR>
NAME 'batas_administrasi_line2_utm'<BR> TYPE LINE<BR>
CONNECTIONTYPE postgis<BR> CONNECTION 'host=localhost
user=rtrw dbname=rtrw-pacitan-trenggalek'<BR> DATA 'the_geom
FROM<BR>batas-admin.batas_administrasi_line2_utm'<BR>=======================================================<BR><BR><BR>Yet,
when I tried to display the mapfile, I got the following error
message:<BR>=======================================================<BR>msDrawMap():
Image handling error. Failed to draw layer
named<BR>'batas_administrasi_line2_utm'.<BR>prepare_database(): Query error.
Error executing POSTGIS DECLARE (the actual<BR>query) statement: 'DECLARE
mycursor BINARY CURSOR FOR
SELECT<BR>asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text<BR>from
batas-admin.batas_administrasi_line2_utm WHERE () and (the_geom
&&<BR>setSRID(
'BOX3D(528656.510416667<BR>9074209,609071.177083333<BR>9134520)'::BOX3D,find_srid('','batas-admin.batas_administrasi_line2_utm','th<BR>e_geom')<BR>))'<BR><BR>Postgresql
reports the error as 'ERROR: syntax error at or near "-" at<BR>character 116
'<BR><BR>More Help:<BR><BR>Error with POSTGIS data variable. You specified
'<check your .map file>'.<BR>Standard ways of specifiying are :<BR>(1)
'geometry_column from geometry_table'<BR>(2) 'geometry_column from (<sub
query>) as foo using unique <column name><BR>using
SRID=<srid#>'<BR><BR>Make sure you put in the 'using unique <column
name>'<BR>and 'using SRID=#' clauses in.<BR><BR>For more help, please
see<BR><A href="http://postgis.refractions.net/documentation.php"
target=_blank>http://postgis.refractions.net/documentation.php</A><BR><BR>Mappostgis.c
- version of Jan
23/2004.<BR>=======================================================<BR><BR>I
have tried the following solution:<BR><BR>(1) changing "FROM" to "from"
(uppercase to lowercase)<BR><BR>(2) enabling oid in my database:<BR>-
uncommenting the line "default_with_oids" in my postgresql.on<BR>- changing
the value to "on"<BR>- restarting posgresql<BR>- re-creating all my postGIS
tables<BR><BR><BR>Yet none of the solution above work. MapServer just keeps
giving me the<BR>error message.<BR><BR>What happens? Anyone know a working
solution?<BR><BR>Thanks,<BR>-Kresh<BR><BR><BR><BR><BR>____________________________________________________________________________<BR>________<BR>You
rock. That's why Blockbuster's offering you one month of Blockbuster<BR>Total
Access, No Cost.<BR><A
href="http://tc.deals.yahoo.com/tc/blockbuster/text5.com"
target=_blank>http://tc.deals.yahoo.com/tc/blockbuster/text5.com</A><BR><BR>__________________________________________________________________<BR>So
be it. If saying "NO" means being alone, then to hell with love,
with<BR>women, with marriage, with God, religions, bars, nightclubs, computer
games,<BR>and all the shit life keeps pumping at me. I'll walk alone, but with
freedom<BR>and a healed
pride.<BR><BR><BR><BR>____________________________________________________________________________<BR>________<BR>You
rock. That's why Blockbuster's offering you one month of Blockbuster<BR>Total
Access, No Cost.<BR><A
href="http://tc.deals.yahoo.com/tc/blockbuster/text5.com"
target=_blank>http://tc.deals.yahoo.com/tc/blockbuster/text5.com</A><BR><BR><BR><BR><BR>------------------------------<BR><BR>Message:
32<BR>Date: Fri, 04 Apr 2008 10:24:21 +0200<BR>From: Barend Kobben <<A
href="mailto:kobben@itc.nl">kobben@itc.nl</A>><BR>Subject: Re:
[postgis-users] Problem with the Codification/charset<BR>
from Postgis to mapserver<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:C41BB255.1BE1%25kobben@itc.nl">C41BB255.1BE1%kobben@itc.nl</A>><BR>Content-Type:
text/plain; charset="ISO-8859-1"<BR><BR>.... also make
sure Mapserevre connects as a UTF-8 client to PG by satining<BR>including in
the CONNECTION object:<BR>options='-c
client_encoding=UTF8'<BR><BR><BR>--<BR>Barend Köbben<BR>International
Institute for Geo-Information<BR>Sciences and Earth Observation (ITC)<BR>PO
Box 6<BR>7500AA Enschede, The Netherlands<BR>+31 (0)53
4874253<BR><BR><BR><BR>On 03-04-2008 18:10, "Stephen Woodbridge" <<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>>
wrote:<BR><BR>> Emilio Ponce wrote:<BR>>> Hi
everyone!<BR>>><BR>>> Recently I've done a simple mapfile
(mapserver) that gets a postgis<BR>>> table and represents it. The
problem is about the codification: The<BR>>> Postgis database is coded
as UTF8 and when I represent a varchar column<BR>>> with mapserver the
accents and especial characters are wrong. What can I<BR>>> do? Can I
easily change the mapserver codification?<BR>><BR>> This is probably
better posted to the mapserver list. But you might try<BR>> adding to your
label block<BR>><BR>> ENCODING "UTF-8"<BR>><BR>> -Steve W<BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR>International
Institute for Geo-Information Science and Earth Observation (ITC)<BR>Chamber
of Commerce: 410 27 560<BR><BR>E-mail disclaimer<BR>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.<BR><BR><BR>------------------------------<BR><BR>Message:
33<BR>Date: Fri, 04 Apr 2008 08:01:31 +0000<BR>From: "Jonathan W. Lowe" <<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users
Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<1207296091.3135.22.camel@localhost.localdomain><BR>Content-Type:
text/plain<BR><BR>On Thu, 2008-04-03 at 20:31 -0400, Stephen Frost
wrote:<BR>> Jonathan,<BR>><BR>> * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
> ...And in case the images don't persist through the mail server,
they're<BR>> > viewable at: <A
href="http://www.giswebsite.com/demos/tiger_overlays.html"
target=_blank>http://www.giswebsite.com/demos/tiger_overlays.html</A><BR>><BR>>
You know, I just realized that you were talking about the Census 2000<BR>>
blocks (tabblock00.shp). Is there some reason you're using that<BR>>
instead of the current data (tabblock.shp)? They might not want
to<BR>> update the data from 2000 for historical reasons...<BR>> (Note:
I havn't actually gone and looked, it just occured to me..)<BR><BR>I haven't
yet confirmed whether the 2007 version of Census Blocks<BR>maintains a 1:1
match with the associated statistics in SF1, so was<BR>starting with the 2000
version. However, positionally, the two Census<BR>Block versions
(tabblock00 and tabblock) are identical. And they both<BR>positionally
match the single 2007 edges data. (I've added a third<BR>screen shot to
<A href="http://www.giswebsite.com/demos/tiger_overlays.html"
target=_blank>www.giswebsite.com/demos/tiger_overlays.html</A>
to<BR>illustrate.)<BR><BR>Has anyone else seen the same misalignment between
TIGER (2007 and/or<BR>2000) when overlaying on other datasets?<BR><BR>It's
still a mystery how OpenStreetMap's tiles for Berkeley, which are<BR>based on
TIGER, don't seem to duplicate the zig-zag qualities of the<BR>edges data I've
downloaded from TIGER 2007. Something is definitely<BR>missing in the
puzzle, but it doesn't sound like the issue is with the<BR>PostGIS conversion
part of the process, so, thanks for the help to this<BR>point -- I'll
check with the OpenStreetMap community next.<BR><BR>><BR>>
Thanks,<BR>><BR>>
Stephen<BR>><BR>> > On Fri, 2008-04-04 at 01:07 +0100,
Jonathan W. Lowe wrote:<BR>> > > Stephen,<BR>> > ><BR>>
> > My initial testing has been on Alameda County (California) TIGER
data.<BR>> > > The two attached image files show an overlay of US
Census 2000 Blocks<BR>> > > over an area south of the UC Berkeley
campus. The offset is the same<BR>> > > for both Google and
OpenStreetMap (OSM). This suggests that I've made a<BR>> > >
mistake somewhere, because the OSM tiles in the United States are all<BR>>
> > rendered from TIGER linework, so the TIGER census blocks should
match<BR>> > > exactly.<BR>> > ><BR>> > > For the
same source shapefile (tabblock00.shp), there's a nearly perfect<BR>> >
> match between block boundaries and streets in the area just South
of<BR>> > > Oakland's Lake Merritt. It smells like a datum
conversion issue...<BR>> > ><BR>> > > The conversion path
was from shapefile to PostGIS using shp2pgsql. I<BR>> > > used
a custom projection of 32767 rather than 4269 because the existing<BR>>
> > srtext for 4269 had a degree value as 0.01745329251994328, but the
US<BR>> > > Census metadata listed a degree value of
0.017453292519943295. Perhaps<BR>> > > not significant?
My spatial_ref_sys entries for 4269 and 32767 are<BR>> > >
otherwise pretty similar:<BR>> > ><BR>> > > SRID:
4269<BR>> > > SRTEXT:
GEOGCS["NAD83",DATUM["North_American_Datum_1983",<BR>> > >
SPHEROID["GRS 1980",6378137,298.257222101,<BR>> > >
AUTHORITY["EPSG","7019"]],<BR>> > >
AUTHORITY["EPSG","6269"]],<BR>> > >
PRIMEM["Greenwich",0,<BR>> > >
AUTHORITY["EPSG","8901"]],<BR>> > >
UNIT["degree",0.01745329251994328,<BR>> > >
AUTHORITY["EPSG","9122"]],<BR>> > >
AUTHORITY["EPSG","4269"]]<BR>> > > PROJ4TEXT:
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs<BR>> > ><BR>>
> > SRID: 32767<BR>> > > SRTEXT:
GEOGCS["GCS_North_American_1983",<BR>> > >
DATUM["D_North_American_1983",<BR>> > >
SPHEROID["GRS_1980",6378137,298.257222101]],<BR>> > >
PRIMEM["Greenwich",0],<BR>> > >
UNIT["Degree",0.017453292519943295]]<BR>> > > PROJ4TEXT:
+proj=longlat +ellps=clrk66 +datum=NAD27 +no_defs<BR>> > ><BR>>
> > To display census block data in OpenStreetMap, I extract it from
PostGIS<BR>> > > with a transform to EPSG 4326, although the
coordinates don't seem to<BR>> > > change as a result. (This
seems correct, as datum=NAD83 and datum=WGS84<BR>> > > are, for my
purposes at least, are essentially identical.)<BR>> > ><BR>> >
> Thanks,<BR>> > > Jonathan<BR>> > ><BR>> > > 2
attachments: TIGER2007andOSM.png, TIGER2007andGoogle.png<BR>> >
><BR>> > ><BR>> > > On Thu, 2008-04-03 at 19:18 -0400,
Stephen Frost wrote:<BR>> > > > Jonathan,<BR>> > >
><BR>> > > > * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>) wrote:<BR>>
> > > > Have you yet tried overlaying TIGER 2007 linework or
census block/tract<BR>> > > > > polygons over Google or
OpenStreetMap tiles? I'm seeing a good match in<BR>> > > >
> some areas but a significant shift (~50 meters) in others. Thought
it<BR>> > > > > might be a datum conversion issue, but can't
seem to find a match.<BR>> > > ><BR>> > > > I hadn't
looked at the linework too much yet or tried to overlay it.<BR>> > >
> I'm curious where you're seeing the differences though because I
know<BR>> > > > that Census is only about half way through their
MAF improvment project<BR>> > > > and I actually have some info
about what has been done so far and what<BR>> > > > hasn't.
It'd be interesting to see if it matches up.<BR>> > >
><BR>> > > > There are a few places (Guam, Hawaii islands)
where they actually do use<BR>> > > > an SRID other than 4269, but
my scripts don't yet handle that and I'm<BR>> > > > guessing
that's not what you're referring to anyway. :)<BR>> > > ><BR>>
> > > Thanks!<BR>> > >
><BR>> > > >
Stephen<BR>> > > ><BR>> > > > > On Thu,
2008-04-03 at 17:07 -0400, Stephen Frost wrote:<BR>> > > > >
> * Stephen Frost (<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>) wrote:<BR>> >
> > > > > I think they may have also upgraded their pipe..
I got about 1.41MB/s<BR>> > > > > > > (11 Mb/s) for
the whole transfer. It's about 22G all told. I'll<BR>> >
> > > > > probably be trying to load it up into PG on one of
our servers tomorrow.<BR>> > > > > > > It was a bit over
4 hours for me to pull down off of their<BR>> > > > > > >
<A href="http://ftp2.census.gov" target=_blank>ftp2.census.gov</A> ftp
site.<BR>> > > > > ><BR>> > > > > > Just
to update those who might be interested- I've finished the data<BR>> >
> > > > load into one of our servers at work. It comes to
~60GB on disk in<BR>> > > > > > PostgreSQL/PostGIS with
appropriate indexes in most places and whatnot.<BR>> > > > >
> Based on what I've seen so far, it looks *very* nice, especially
the<BR>> > > > > > hydrogrophy ("areawater"). It also
appears to be pretty consistant<BR>> > > > > > across the
layers, which is also good.<BR>> > > > > ><BR>> > >
> > > If anyone's interested in the scripts used to load the data
(they're<BR>> > > > > > pretty simple, really), I'd be happy
to provide them.<BR>> > > > > ><BR>> > > > >
> Enjoy,<BR>> > > > > ><BR>> > >
> > > Stephen<BR>> >
> > > > _______________________________________________<BR>>
> > > > > postgis-users mailing list<BR>> > > >
> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > > > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> > > ><BR>> > > > >
_______________________________________________<BR>> > > > >
postgis-users mailing list<BR>> > > > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> > _______________________________________________<BR>> >
postgis-users mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>------------------------------<BR><BR>Message:
34<BR>Date: Fri, 4 Apr 2008 07:04:50 -0400<BR>From: Stephen Frost <<A
href="mailto:sfrost@snowman.net">sfrost@snowman.net</A>><BR>Subject: Re:
[postgis-users] TIGER/Line Shapefiles released<BR>To: PostGIS Users Discussion
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<<A
href="mailto:20080404110450.GF4999@tamriel.snowman.net">20080404110450.GF4999@tamriel.snowman.net</A>><BR>Content-Type:
text/plain; charset="us-ascii"<BR><BR>Steve,<BR><BR>* Stephen Woodbridge (<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>)
wrote:<BR>> OK, Now I'm feeling kind of dumb.<BR><BR>You shouldn't, that
was my goof. :)<BR><BR>> ++ getopt -o n::ls::bc::ed -n process_tiger.sh --
-d -n<BR><BR>That should have been 'nl::s::...'. I've fixed that in my
script,<BR>please give it another go. I made some changes (like adding
parameters,<BR>heh) after I got the data loaded and so havn't completely
retested<BR>everything. Actually, I've made some further
updates/improvments which<BR>will help (like, I dunno, error
checking...).<BR><BR> Thanks!<BR><BR>
Stephen<BR>-------------- next part
--------------<BR>A non-text attachment was scrubbed...<BR>Name: not
available<BR>Type: application/pgp-signature<BR>Size: 189 bytes<BR>Desc:
Digital signature<BR>Url : <A
href="http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/e6024002/attachment-0001.bin"
target=_blank>http://lists.refractions.net/pipermail/postgis-users/attachments/20080404/e6024002/attachment-0001.bin</A><BR><BR>------------------------------<BR><BR>Message:
35<BR>Date: Fri, 4 Apr 2008 14:52:38 +0300<BR>From: "Nick Black" <<A
href="mailto:nickblack1@gmail.com">nickblack1@gmail.com</A>><BR>Subject:
Re: [postgis-users] TIGER/Line Shapefiles released<BR>To: "Jonathan W. Lowe"
<<A href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>>,
"PostGIS Users<BR> Discussion" <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:<BR>
<<A
href="mailto:223020e60804040452i15f6e3b3qd84ec37225edb170@mail.gmail.com">223020e60804040452i15f6e3b3qd84ec37225edb170@mail.gmail.com</A>><BR>Content-Type:
text/plain; charset=ISO-8859-1<BR><BR>On Fri, Apr 4, 2008 at 11:01 AM,
Jonathan W. Lowe <<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>> wrote:<BR>>
On Thu, 2008-04-03 at 20:31 -0400, Stephen Frost wrote:<BR>> >
Jonathan,<BR>> ><BR>> > * Jonathan W. Lowe (<A
href="mailto:jlowe@giswebsite.com">jlowe@giswebsite.com</A>)
wrote:<BR>><BR>>...<BR><BR>[Message
clipped] </BLOCKQUOTE></DIV><BR><BR clear=all><BR>-- <BR>Dylan
Lorimer | Strategic Partner Management<BR>415.573.2909 (Grand Central) |
650.644.0182 (Fax)<BR><BR>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.
</BODY></HTML>