| 08:34:59 | sigq: | geosfeed: WikiStart edited by pramsey <http://trac.osgeo.org/geos/wiki/WikiStart> |
| 10:10:33 | louk: | i'm having some trouble with this query |
| 10:10:34 | louk: | $query = "select tiger_us.state.name from tiger_us.state where st_contains( tiger_us.state.the_geom, st_setsrid( 'POINT( ".$startlat." ".$startlng." )', 4326 ) )"; |
| 10:12:36 | louk: | Operation on two geometries with different SRIDs |
| 10:17:21 | louk: | i must have the wrong EPSG for North_American_Datum_1983 .. |
| 10:19:24 | louk: | answer is ~ 4269 |
| 10:25:59 | darkblue_B: | bingo |
| 10:26:30 | darkblue_B: | what language are you using? |
| 10:26:43 | darkblue_B: | is that inside the DB, or external? |
| 10:26:52 | darkblue_B: | could be perl |
| 10:30:19 | louk: | php |
| 10:30:44 | louk: | seems to be failing silently |
| 10:31:00 | louk: | here is a more eligible version |
| 10:31:01 | louk: | select tiger_us.state.name, tiger_us.state.statefp from tiger_us.state where st_contains( tiger_us.state.the_geom, st_setsrid( 'POINT( 40.708870 -73.965523 )', 4269 ) ) |
| 10:31:58 | darkblue_B: | can you see your postgres log? |
| 10:34:08 | darkblue_B: | ( no index on statefp for me... this might take a moment ) |
| 10:43:21 | sindile: | I have two polylines ( streets ) that are side by side and would like to create one polyline, and the attributes are the same - in essence I want to stitch the two. How do i accomplish this |
| 10:44:09 | darkblue_B: | you want to append.. one MULTIILINESTRING to a 2nd? |
| 10:45:17 | sindile: | darkblue_B: yes and have a one layer |
| 10:49:50 | darkblue_B: | louk: there are several ways of creating POINTS in line.. makepoint( lat,lng ) is one |
| 10:50:02 | darkblue_B: | I'm not sure passing raw WKT like that will work |
| 10:50:57 | darkblue_B: | st_geomfromwkt is another |
| 10:57:07 | darkblue_B: | *ewkt*.. so its st_geomfromewkt( 'SRID=4269;POINT( 40.7 -73.2 )' ) |
| 11:07:55 | darkblue_B: | sindile: use ST_LineMerge( ), if it is available on your system |
| 11:09:34 | sindile: | darkblue_B:thanks |
| 11:20:41 | darkblue_B: | louk: st_contains( ) is ( poly, point ).. st_within( ) is ( point, poly ).. I bet |
| 11:24:54 | darkblue_B: | using && for intersect gets you off that hook |
| 11:33:04 | bcrosby: | anyone around? |
| 11:33:13 | bcrosby: | have a question on how I should represent something in postgis |
| 11:34:23 | darkblue_B: | ok |
| 11:35:35 | bcrosby: | one sec : ) |
| 11:36:40 | bcrosby: | www.blakecrosby.com/postgis.txt |
| 11:37:00 | bcrosby: | thats the legal definition of an area in Canada |
| 11:37:09 | bcrosby: | and I have no idea how I can store that in my postgres database |
| 11:38:08 | darkblue_B: | looking |
| 11:38:37 | bcrosby: | POLYGON would do the trick, except for the fact there is an arc ; ) |
| 11:40:53 | darkblue_B: | hmm even inPostIS 1.4, which is not out yet, circular/curve/arc things are not "fully supported" |
| 11:41:24 | darkblue_B: | I think what people ^Y^Y^Y^Y^Y computers do is aprox with points |
| 11:41:31 | darkblue_B: | so it is a Polygon or linestring |
| 11:42:25 | darkblue_B: | there are wizards that may know how to use PostGIS to create the aprox., but myself, I sould look to other means |
| 11:42:44 | darkblue_B: | I am a programmer.. SQL is a distant second for me.. I just struggle through it |
| 11:44:20 | bcrosby: | heh |
| 11:44:21 | bcrosby: | thanks |
| 11:44:27 | bcrosby: | maybe I will post to the mailing list |
| 14:04:27 | louk: | darkblue_B: thanks for the tips earlier |
| 14:05:17 | louk: | looks like i've got the basics up and running |
| 14:06:27 | louk: | now i need to detect if a point is close to the border and should be verified by street address ( in case its on the wrong side of the street ) |
| 14:07:54 | louk: | ran an st_contains last time |
| 14:17:57 | darkblue_B: | louk: you are welcome |
| 14:19:05 | darkblue_B: | so what indexes do you have on fe_edges? |
| 14:28:12 | louk: | i'm not sure i have any |
| 14:36:44 | darkblue_B: | ??1 |
| 14:36:49 | darkblue_B: | how can that be |
| 14:37:09 | darkblue_B: | its 62m records.. any query at all would take minutes |
| 14:38:47 | darkblue_B: | I just have the GIST index on the_geom right now |
| 14:38:58 | darkblue_B: | ..was considering putting a few more in |
| 14:42:54 | darkblue_B: | so I just ran a query.. statefp = "78"... ok, modern machine, SATA disk, nothing fancier than that |
| 14:49:34 | darkblue_B: | ~73 mb/sec reads.... memory cpu ~700mb/sec.. dual core |
| 14:49:53 | darkblue_B: | its been 7 minutes so far.. not done yet |
| 14:50:17 | darkblue_B: | so.. if you have the whole 2007 TIGER loaded, someone has got to have made some indexes |
| 14:50:40 | darkblue_B: | if you look at the table they are generally listed somewhere |
| 18:16:57 | bcrosby: | hahah |
| 18:17:16 | bcrosby: | whoops |
| 19:36:17 | louk: | darkblue_B: the import log shows that some indexes were added. i'm not sure where exactly but i'd like to figure that out. |
| 19:37:08 | louk: | my searches are moving very quickly, i'm doing four successive queries in a matter of milliseconds |
| 19:37:32 | louk: | looks like the db is 43 gb |
| 19:38:19 | louk: | its running on a vmware server with dual processors enabled, 3.8 gb ram allocated |
| 19:39:24 | louk: | host os is a dual processor, quad-core 2.16 ghz xeon with 12 gb ram, raid 5 1 tb hd array ( 7200 rpm drives ) |
| 19:40:48 | darkblue_B: | yow - that rocks |
| 19:41:41 | darkblue_B: | is you use psql.. \c to the tiger_us database, then \d fe_edges, you'll see the indexes listed.. ( something like those names ) |
| 19:42:06 | louk: | it'll have to share that hardware with other apps in the future but for now its running pretty much solo |
| 19:44:01 | louk: | hmmm. threw an error |
| 19:44:35 | louk: | Indexes: |
| 19:44:35 | louk: | "state_pkey" PRIMARY KEY, btree ( gid ) |
| 19:44:35 | louk: | "state_the_geom_gist" gist ( the_geom ) |
| 19:46:02 | darkblue_B: | thats all then |
| 19:46:10 | darkblue_B: | GIST on the_geom |
| 19:46:48 | louk: | seems like the script put a GIST on every the_geom |
| 19:47:01 | darkblue_B: | you are doing within( ) or something, on a POINT.. thats pure the_geom... |
| 19:47:25 | darkblue_B: | if you said AND statefp = blah or something, that would be slower |
| 19:47:36 | darkblue_B: | .. if that makes sense |
| 19:47:37 | louk: | ahhh |
| 19:47:53 | louk: | it does, though i haven't gone there yet |
| 19:47:54 | darkblue_B: | >script.. GIST.. indexes are built once |
| 19:48:05 | darkblue_B: | ahead of time |
| 19:48:14 | louk: | gotcha |
| 19:48:18 | darkblue_B: | so.. of you are going to be searching including some other criteria |
| 19:48:25 | louk: | do i need to worry about vacuum? |
| 19:48:43 | darkblue_B: | you might want to create index idx_etc on field.. whatever |
| 19:48:58 | louk: | whats the drawback of adding indexes? |
| 19:49:12 | louk: | other than upfront processing time |
| 19:49:14 | darkblue_B: | yes.. you have to vacuum analyze after the index is created to let the engine know about the new optimization for queries |
| 19:49:37 | darkblue_B: | hmm.. I'm not an expert.. they take space.. if you change the data, they are out of date |
| 19:49:48 | darkblue_B: | so .. read-only vs r/w is crucial |
| 19:49:58 | darkblue_B: | this data is r/o |
| 19:50:05 | louk: | makes sense |
| 19:50:09 | darkblue_B: | and its huge! |
| 19:50:22 | darkblue_B: | so indexes are a no-brainer I think |
| 19:50:38 | darkblue_B: | if your data is 40+gig.. I think someone loaded a lot of the TIGER data |
| 19:50:57 | darkblue_B: | I just have the edges.. eg.. the street MULTILINESTRINGs |
| 19:51:04 | darkblue_B: | thats about 20 g |
| 19:52:41 | louk: | i think i'm missing a few pieces |
| 19:53:06 | louk: | addrange / addrfeatures - i think |
| 19:58:37 | darkblue_B: | pramsey: hello.. any downside to indexes in general, on static read-only data? |
| 19:59:13 | darkblue_B: | ( he disappeared ) |
| 19:59:29 | pramsey: | say that again, sometime colloquy eats words... |
| 20:00:18 | pramsey: | repetez le question, s'il vous plait |
| 20:00:34 | darkblue_B: | pramsey: hello.. any downside to indexes in general, on static read-only data? |
| 20:00:40 | pramsey: | none |
| 20:00:53 | darkblue_B: | louk: there ya go |
| 20:06:03 | louk: | good stuff |