[postgis-users] Re: roadmap data files
Mark Thomas
spatialguru.net at gmail.com
Wed Dec 27 18:27:58 PST 2006
RoadMap just uses TIGER/line files from the census bureau so you can use
ogr2ogr to import the TIGER files into PostGIS. further processing for
roads can be done on the COMPLETECHAINS table once the TIGER files are
loaded into PostGIS.
On 12/27/06, postgis-users-request at postgis.refractions.net <
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: Looking for more performance on select (Obe, Regina)
> 2. RE: Looking for more performance on select (Obe, Regina)
> 3. roadmap data files (alex bodnaru)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 26 Dec 2006 14:44:58 -0500
> From: "Obe, Regina" <robe.dnd at cityofboston.gov>
> Subject: RE: [postgis-users] Looking for more performance on select
> To: <ericfrancois at hotmail.com>, "PostGIS Users Discussion"
> <postgis-users at postgis.refractions.net>
> Message-ID:
> <53F9CF533E1AA14EA1F8C5C08ABC08D20CBAA4 at ZDND.DND.boston.cob>
> Content-Type: text/plain; charset="iso-8859-1"
>
> I would change your index to a clustered index on your id field in
> streets_db_edges
>
> For your other table streets_db_big - I'm guessing a clustered index on
> your the_geom field would give you better performance than a cluster on the
> edge_id field. Having clustered indexes has improved my speeds a lot.
>
>
> ________________________________
>
> From: postgis-users-bounces at postgis.refractions.net on behalf of Eric
> FRANCOIS
> Sent: Tue 12/26/2006 10:12 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Looking for more performance on select
>
>
>
> Hello,
>
> I'm looking for help to improve postgres performance query.
>
> I've 2 tables:
> streets_db_egdes with 6 millions record, field 'id' is the primary of the
> table
> streets_db_big with 800000 records with an index on a field named edge_id
> and on a geometry field name the_geom.
>
> vaccum analyse is run on the database twice a day.
>
> The following query is runned in 15 seconds:
> SELECT gid as id FROM streets_db_big as m
> where setsrid('BOX3D(1.65924 43.38176,3.16274 50.73713)'::BOX3D,4326) &&
> m.the_geom
>
>
>
>
> The query with a join between the 2 table runs in near 60s:
> SELECT gid as id, source::integer, target::integer, cost::double precision
> as cost,reverse_cost::double precision as reverse_cost FROM
> streets_db_edges
> as s,streets_db_big as m
> where m.edge_id=s.id and setsrid('BOX3D(1.65924 43.38176,3.16274
> 50.73713)'::BOX3D,4326) && m.the_geom
>
> the explain:
>
> Hash Join (cost=85806.40..329366.66 rows=174676 width=28) (actual
> time=22946.953..56012.747 rows=164889 loops=1)
> Hash Cond: (s.id = m.edge_id)
> -> Seq Scan on streets_db_edges s (cost=0.00..153395.40 rows=5894540
> width=28) (actual time=16.912..17790.444 rows=5894540 loops=1)
> -> Hash (cost=85369.71..85369.71 rows=174676 width=8) (actual
> time=22898.034..22898.034 rows=164889 loops=1)
> -> Bitmap Heap Scan on streets_db_big m (cost=5530.26..85369.71
> rows=174676 width=8) (actual time=5446.865..22464.712 rows=164889 loops=1)
> Filter:
>
> ('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70033E
>
> 3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940E275
> FD82DDB04540CE70033E3F8CFA3FE275FD82DDB04540'::geometry && the_geom)
> -> Bitmap Index Scan on streets_db_big_the_geom_idx
> (cost=0.00..5530.26 rows=174676 width=0) (actual time=5437.166..5437.166
> rows=164889 loops=1)
> Index Cond:
>
> ('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70033E
>
> 3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940E275
> FD82DDB04540CE70033E3F8CFA3FE275FD82DDB04540'::geometry && the_geom)
> Total runtime: 56367.929 ms
>
>
> Is there a way to increase my performances in this type of select queries?
>
>
> Thanks for help
>
> ERIC
>
> My conf:
>
> Toshiba Tecra A4 with 1 Go Ram running PostgreSQl 8.2
> Database on external disk, pg_xlog on local disk
>
> shared_buffers = 228MB
> work_mem=50MB
> maintenance_work_mem=100MB
> effective_cache_size = 500MB
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally
> privileged and/or exempt from disclosure pursuant to Massachusetts
> law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and
> delete the material from any computer.
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/ms-tnef
> Size: 6264 bytes
> Desc: not available
> Url :
> http://lists.refractions.net/pipermail/postgis-users/attachments/20061226/6a95f83a/attachment-0001.bin
>
> ------------------------------
>
> Message: 2
> Date: Tue, 26 Dec 2006 15:33:27 -0500
> From: "Obe, Regina" <robe.dnd at cityofboston.gov>
> Subject: RE: [postgis-users] Looking for more performance on select
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <53F9CF533E1AA14EA1F8C5C08ABC08D2014DA040 at ZDND.DND.boston.cob>
> Content-Type: text/plain; charset="us-ascii"
>
> I just noticed something else in your explain. Its doing a seq scan on
> your streets_db_edges. I would have expected that to be an index scan.
>
> Are the data types of your id and edge_id of the same type? If they
> aren't then it would do a seq scan instead of indexed scan. I know at
> least in earlier versions if you had an int8 in one table and an int4
> in another, it would not do an index scan. This might have changed in
> the 8.2 version.
>
> ________________________________
>
> From: Obe, Regina
> Sent: Tue 12/26/2006 2:44 PM
> To: ericfrancois at hotmail.com; PostGIS Users Discussion
> Subject: RE: [postgis-users] Looking for more performance on select
>
>
> I would change your index to a clustered index on your id field in
> streets_db_edges
>
> For your other table streets_db_big - I'm guessing a clustered index on
> your the_geom field would give you better performance than a cluster on
> the edge_id field. Having clustered indexes has improved my speeds a
> lot.
>
>
> ________________________________
>
> From: postgis-users-bounces at postgis.refractions.net on behalf of Eric
> FRANCOIS
> Sent: Tue 12/26/2006 10:12 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Looking for more performance on select
>
>
>
> Hello,
>
> I'm looking for help to improve postgres performance query.
>
> I've 2 tables:
> streets_db_egdes with 6 millions record, field 'id' is the primary of
> the
> table
> streets_db_big with 800000 records with an index on a field named
> edge_id
> and on a geometry field name the_geom.
>
> vaccum analyse is run on the database twice a day.
>
> The following query is runned in 15 seconds:
> SELECT gid as id FROM streets_db_big as m
> where setsrid('BOX3D(1.65924 43.38176,3.16274 50.73713)'::BOX3D,4326)
> &&
> m.the_geom
>
>
>
>
> The query with a join between the 2 table runs in near 60s:
> SELECT gid as id, source::integer, target::integer, cost::double
> precision
> as cost,reverse_cost::double precision as reverse_cost FROM
> streets_db_edges
> as s,streets_db_big as m
> where m.edge_id=s.id and setsrid('BOX3D(1.65924 43.38176,3.16274
> 50.73713)'::BOX3D,4326) && m.the_geom
>
> the explain:
>
> Hash Join (cost=85806.40..329366.66 rows=174676 width=28) (actual
> time=22946.953..56012.747 rows=164889 loops=1)
> Hash Cond: (s.id = m.edge_id)
> -> Seq Scan on streets_db_edges s (cost=0.00..153395.40 rows=5894540
> width=28) (actual time=16.912..17790.444 rows=5894540 loops=1)
> -> Hash (cost=85369.71..85369.71 rows=174676 width=8) (actual
> time=22898.034..22898.034 rows=164889 loops=1)
> -> Bitmap Heap Scan on streets_db_big m
> (cost=5530.26..85369.71
> rows=174676 width=8) (actual time=5446.865..22464.712 rows=164889
> loops=1)
> Filter:
> ('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70
> 033E
> 3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940
> E275
> FD82DDB04540CE70033E3F8CFA3FE275FD82DDB04540'::geometry && the_geom)
> -> Bitmap Index Scan on streets_db_big_the_geom_idx
> (cost=0.00..5530.26 rows=174676 width=0) (actual time=5437.166..5437.166
> rows=164889 loops=1)
> Index Cond:
> ('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70
> 033E
> 3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940
> E275
> FD82DDB04540CE70033E3F8CFA3FE275FD82DDB04540'::geometry && the_geom)
> Total runtime: 56367.929 ms
>
>
> Is there a way to increase my performances in this type of select
> queries?
>
>
> Thanks for help
>
> ERIC
>
> My conf:
>
> Toshiba Tecra A4 with 1 Go Ram running PostgreSQl 8.2
> Database on external disk, pg_xlog on local disk
>
> shared_buffers = 228MB
> work_mem=50MB
> maintenance_work_mem=100MB
> effective_cache_size = 500MB
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally
> privileged and/or exempt from disclosure pursuant to Massachusetts
> law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and
> delete the material from any computer.
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20061226/221768ae/attachment-0001.html
>
> ------------------------------
>
> Message: 3
> Date: Wed, 27 Dec 2006 03:09:40 +0200
> From: alex bodnaru <alexbodn at 012.net.il>
> Subject: [postgis-users] roadmap data files
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4591C7D4.8080002 at alex3>
> Content-Type: text/plain; charset=ISO-8859-1
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> hi all,
>
> i wish to load to postgis database data that is currently formatted for
> roadmap (http://roadmap.digitalomaha.net/).
>
> have you done this, by any chance?
>
> tia,
>
> alex
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iQCVAwUBRZHH1NpwN1sq38njAQJZ0AP/SfBFQzPu2O8ygnzn1Q0Cns0j81GaYMi7
> /yOt9UaQXEGPlev6Wd+iJbZrsYT/+rvdwPZLZO1t7RKFk38YdiIxA1XNVVMv8JO3
> fWrA/z1tTblOSwIs/o8VSWxZUBT9iy6zH8GY8NZzSv467WZlIkb6aCVNUZgDDbUg
> Mo/FxuCC0V8=
> =27pV
> -----END PGP SIGNATURE-----
>
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 50, Issue 25
> *********************************************
>
--
Regards,
Mark Thomas
spatialguru.net at gmail.com
205.529.9013
"Commit to the Lord whatever you do,
and your plans will succeed." - Proverbs 16:3
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061227/74d16714/attachment.html>
More information about the postgis-users
mailing list