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