| 03:33:25 | CIA-6: | robe * r2914 /trunk/doc/ ( reference.xml reference_new.xml ): got rid of additional entry for ST_GeomFromText - was already moved over, moved over ST_PointFromText, ST_MakePoint |
| 03:50:59 | CIA-6: | robe * r2915 /trunk/doc/ ( reference.xml reference_new.xml ): Moved over ST_SRID, fixed a typo, added some references |
| 06:07:28 | iggy: | jlivni: everyone else who helped me yesterday: thank you thank you! Time: 0.845 ms |
| 07:29:29 | darkblue_B: | iggy: so whats an example of a query? |
| 07:30:08 | iggy: | darkblue_B: the simple one that I started with was: select count( * ) from addresses where st_within( addr_point, st_expand( ST_SetSRID( ST_MakePoint( 29.742028, -095.399748 ), 4326 ), .001 ) ); |
| 07:32:47 | darkblue_B: | st_within( .. st_expand( .. MakePoint( ) ) ) |
| 07:34:05 | iggy: | huh? are you saying I don't need the setsrid? |
| 07:34:15 | darkblue_B: | no |
| 07:34:19 | darkblue_B: | just simplifying |
| 07:34:54 | darkblue_B: | I'm going to try that here on another DB just to see |
| 07:35:32 | darkblue_B: | supposedly there will be 'PostGIS hacking' tomorrow with TheSteveMonkey at Linux World |
| 07:35:37 | iggy: | I'm happy with the performance |
| 07:35:51 | darkblue_B: | since there are *so* any possible use cases |
| 07:35:58 | darkblue_B: | I like to try things as they come up |
| 07:36:06 | darkblue_B: | yeah, sounds like you are rocking |
| 07:36:26 | iggy: | I was actually expecting something closer to like 20-30 secs |
| 07:36:57 | iggy: | so <1sec is spectacular |
| 07:37:23 | darkblue_B: | indexes.. cant beat that |
| 07:37:51 | darkblue_B: | since I dont have the TIGER data, or OpenStreetMap loaded |
| 07:37:53 | iggy: | and being able to keep the 2 10G indexes in memory |
| 07:38:08 | darkblue_B: | the biggest DB I have on line is 2.6 million cities as 4326 points |
| 07:38:30 | iggy: | how did you come by that data? |
| 07:38:33 | darkblue_B: | ohh.. can you tell your indexes are in RAM ? |
| 07:38:51 | iggy: | well, I've got enough ram to hold them, so I hope so |
| 07:39:02 | darkblue_B: | I found it on the Interweb ;- ) after x 100 hours of reading mailing lists and archives and such! |
| 07:39:29 | darkblue_B: | I have to admit, I had to get help to import it.. ^&#*&$^ unix tricks |
| 07:39:31 | : | * iggy vows to put a page together with all he's learned in this "adventure" |
| 07:39:57 | darkblue_B: | something about the encoding... it has all the UTF local names right |
| 07:40:11 | darkblue_B: | page -> definitely |
| 07:46:08 | darkblue_B: | in my PostGIS, makepoint( ) expand( ) and setSRID( ) dont have st_ versions |
| 07:46:32 | iggy: | I'm using what's in ubuntu 8.04 |
| 07:47:25 | iggy: | 1.3.3 |
| 07:47:46 | darkblue_B: | select postgis_version( ); |
| 07:47:49 | darkblue_B: | ah so |
| 07:48:21 | iggy: | 1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 |
| 07:53:27 | darkblue_B: | so a similar st_within( .. st_expand( .. MakePoint( ) ) ), on 2.6 m cities.. is coming back with ~350 results in my case in 4.2 seconds |
| 07:54:01 | darkblue_B: | searching in a degree area in N America |
| 07:55:09 | jlivni: | iggy: glad to hear that worked out. if you need stuff within the actual buffer, rather than the expanded bbox, use st_buffer instead of st_expand |
| 07:55:50 | jlivni: | if you don't have lots of results, then it won't make a huge difference in time, since it will only have to calc the relatively complex point-in-circle vs point-in-box for the subset that match the extent |
| 07:57:36 | darkblue_B: | expand is slightly faster for me here |
| 07:57:57 | darkblue_B: | there is no st_expand.. just expand.. postgis 1.2 |
| 07:59:30 | jlivni: | darkblue_B: yes - the st_stuff has been added in recent versions. upgrade to get the latest. |
| 08:00:23 | darkblue_B: | jlivni: I would like to blow away this whole install |
| 08:00:31 | darkblue_B: | its been nothing but a pain |
| 08:00:40 | darkblue_B: | it was the first linux box I set up a year ago |
| 08:01:08 | darkblue_B: | install Postgres 8.3 |
| 08:01:32 | darkblue_B: | there are two disks.. and I'd lke to put $DATA on the other disk |
| 08:01:48 | darkblue_B: | but when asking about that the Postgres people steered me away from it |
| 08:01:53 | darkblue_B: | perhaps wasy to screw up |
| 08:02:20 | darkblue_B: | I am thinking of trying to change it tomorrow .. while at Pg Day at Linux World in SF |
| 08:08:44 | prakriti: | i'm having an srid problem |
| 08:09:00 | prakriti: | is there anywhere to get updated spatial_ref_sys lines? |
| 08:09:25 | darkblue_B: | I use the ones from spatialreference.org |
| 08:09:49 | darkblue_B: | though I take out the 9 the prepend to the ID |
| 08:41:08 | prakriti: | thanks dark |
| 08:41:11 | prakriti: | i found one that worked |
| 08:41:19 | prakriti: | that site it pretty nice |
| 09:02:03 | tbnorth: | hi all - when my table's created by a select it already has a geometry column, I'm trying to use AddGeometryColumn to register that column, but it's also trying to add it to the table, which fails. |
| 09:02:20 | tbnorth: | Should I update geometry_columns myself? |
| 09:08:35 | tbnorth: | That worked, but is there a better way for "create table as select * from" cases? |
| 09:19:38 | jlivni: | tbnorth: what do you mean 'to register that column' ... |
| 09:22:47 | bitnerd: | jlivni: I think he means to add a record to the geometry_columns table and to add constraints to the field |
| 09:31:24 | tbnorth: | jlivni: bitnerd is correct. Otherwise subsequent queries fail. |
| 09:32:14 | tbnorth: | although I missed the "add constraints to the field part", more reason to want to find an better way |
| 09:32:29 | bitnerd: | tbnorth: what do you mean by subsequent queries fail? |
| 09:32:48 | bitnerd: | as far as postgis is concerned geometry_columns table is not necessary at all |
| 09:32:52 | bitnerd: | many clients use it though |
| 09:33:16 | bitnerd: | I have many columns in my db that have never been present in g_c |
| 09:33:35 | tbnorth: | hmm, scrolling back I guess the error was "couldnt find the corresponding SRID" - I should just force it to -1 ? |
| 09:34:57 | bitnerd: | or add that srid to your spatial_ref_systems table which would be best |
| 09:35:17 | bitnerd: | http://spatialreference.org can help with that |
| 09:35:18 | sigq: | Title: Home -- Spatial Reference ( at spatialreference.org ) |
| 09:35:40 | tbnorth: | ok, thanks, seems less trouble than updating g_c |
| 09:35:47 | : | * darkblue_B wonders just what works and doesnt work without g_C.. |
| 09:36:01 | : | * darkblue_B has pressing work now though |
| 09:36:32 | bitnerd: | everything works and nothing doesn't |
| 09:36:49 | bitnerd: | some client apps require it though |
| 09:37:00 | bitnerd: | and it is required to meet standards |
| 09:37:15 | bitnerd: | but internally, it isn't used for anything |
| 09:37:39 | bitnerd: | constraints however can help keep you from doing stupid things |
| 10:03:45 | tbnorth: | IntegrityError: duplicate key value violates unique constraint "geometry_columns_pk" |
| 10:04:05 | tbnorth: | blah - updating g_c definitely more trouble than it's worth |
| 11:52:28 | sindile: | any idea on how to import multiple shapefiles into postgis using either ogr2ogr, shp2pgsql |
| 11:53:21 | bitnerd: | multiple shapefiles into different tables or appended into one table? |
| 11:53:50 | sindile: | bitnerd: into different tables |
| 11:54:23 | bitnerd: | I would just use a shell script and loop through calling shp2pgsql or ogr2ogr several times |
| 11:56:22 | sindile: | bitnerd: thanks but I use Windows and have no clue on how to write a script |
| 11:57:24 | TheSteveMonkey: | use a batch file then |
| 11:57:32 | TheSteveMonkey: | and concat them together after you run it several times |
| 11:57:52 | TheSteveMonkey: | or you can run ogr2ogr if you need to project the files |
| 11:57:59 | TheSteveMonkey: | otherwise just use shp2pgsql |
| 11:58:10 | TheSteveMonkey: | and then takes the contents of each file and put them in one large file |
| 12:19:51 | darkblue_B: | TheSteveMonkey: hey |
| 12:37:26 | iggy: | does anybody have/know of any data tieing county|zipcode|etc to a lat/lon? |
| 13:05:33 | TheSteveMonkey: | darkblue_B: are you getting my PMs |
| 13:06:31 | TheSteveMonkey: | iggy: census shapefiles have center point for most of those layers - if not you can put them into PostGIS and then ask for a point on the lane and store it as a new variable |
| 15:25:38 | TheSteveMonkey: | sorry - that was point on the plane |