| 05:26:02 | TheSteveMonkey: | my talk is up on slideshare - feedback welcome |
| 05:26:03 | TheSteveMonkey: | http://www.slideshare.net/scitronpousty/using-post-gis-to-add-some-spatial-flavor-to-your-application |
| 05:26:04 | sigq: | Title: Using PostGIS To Add Some Spatial Flavor To Your Application ( at www.slideshare.net ) |
| 06:16:21 | darkblue_B: | g'morning springmeyer |
| 06:16:55 | springmeyer: | good morning |
| 06:17:20 | darkblue_B: | ... about to go outside.. but PostGIS 'hacking' later today |
| 06:17:39 | darkblue_B: | .. also the OpenStreetMap "dev day" was the other day |
| 06:18:04 | darkblue_B: | about 20+ showed up I think.. I was there |
| 06:18:15 | springmeyer: | ah cool. |
| 06:18:21 | springmeyer: | down in SF? |
| 06:18:22 | Devastatr: | ahh cool |
| 06:18:23 | Devastatr: | where was it? |
| 06:18:33 | darkblue_B: | Menlo Park |
| 06:19:15 | darkblue_B: | .. near Palo Alto.. but, their office space is actually about a mile from the goos places.. acros the line into the ghetto :- ) |
| 06:19:25 | darkblue_B: | s/goos/good/ |
| 06:20:20 | darkblue_B: | Tiles for Mobile figured prominently |
| 06:25:09 | springmeyer: | enjoy |
| 06:33:22 | melter: | i'm having problems transforming to/from 2163, basically the same problem described at http://postgis.refractions.net/pipermail/postgis-users/2008-January/018383.html |
| 06:33:23 | sigq: | Title: [postgis-users] Weird behavior transforming from srid 4269 to 2163 ( at postgis.refractions.net ) |
| 06:35:05 | mloskot: | melter: so, you've identified your problem already :- ) |
| 06:36:28 | melter: | yes, and the solution is there, too, adding "+towgs84=0,0,0"; my question is, what's the best way to do that from sql? |
| 06:37:06 | melter: | will modifying spatial_ref_sys work, and is that the best solution? |
| 06:37:20 | mloskot: | yes, as Frank suggested |
| 06:37:48 | mloskot: | you need to insert new record with custom definition. it will get new SRID value which you can use in your transformations, instead of 2163 |
| 06:37:57 | melter: | so i'll have to do it again when i upgrade postgis? |
| 06:38:17 | melter: | is there a number reserved for user definitions? |
| 06:38:19 | melter: | srid? |
| 06:39:00 | melter: | i suppose it'd be safe to use 1000000000 : ) |
| 06:40:03 | melter: | so there's no way to do it on the fly, in sql, without modifying that table? |
| 06:43:55 | mloskot: | melter: check schema of spatial_ref_sys |
| 06:44:15 | mloskot: | and you can observe that SRID is a primary key, so it is promised to be unique |
| 06:44:35 | mloskot: | assign max( srid ) + 1 and you are cool |
| 06:44:52 | mloskot: | re the fly, no I'm aware of |
| 06:45:10 | melter: | my main concern is what happens during a postgis upgrade |
| 06:46:03 | melter: | max( srid )+1 might be a valid epsg number some day, then i'll need to reset the srid of my geometries |
| 06:46:28 | melter: | no big deal, but it's just one more maintenance issue |
| 06:46:57 | mloskot: | melter: srid should be considered as unique within your database |
| 06:47:08 | mloskot: | srid != epsg code |
| 06:47:21 | mloskot: | if you are talking of epsg, then auth_srid is the value |
| 06:47:44 | mloskot: | however, I've never upgraded any postgis installation, so I'm not aware of the exact procedure, but I'd not be worried about it |
| 06:48:00 | mloskot: | just write your INSERT instruction to a file and keep it for future |
| 06:48:00 | bitner: | this is why spatialreference.org starts all srids with 9 when creating postgis insert statements |
| 06:48:14 | bitner: | there are no epsg numbers that begin with 9 |
| 06:48:16 | mloskot: | yes, and this is probably best approach |
| 06:49:23 | melter: | if i make it a large number, all 9's, it'll stand out as unusual to future maintainers as well |
| 06:49:49 | melter: | i've been writing some sql functions for maintenance, so i can probably just stick it in there |
| 06:50:13 | mloskot: | melter: you can not avoid potential problems when you will be upgrading postgis installation with custom stuff inside |
| 06:50:24 | mloskot: | taht's why I'm suggesting to backup your customizations |
| 06:50:24 | melter: | i can have a post_postgis_upgrade( ) function that checks if everything is ok |
| 06:50:49 | : | * mloskot has to run |
| 06:50:55 | melter: | mloskot: thank you! |
| 08:10:28 | zanberdo: | just curious if anyone will be at LinuxWorld in SF today. |
| 08:14:18 | jlivni_: | zanberdo: I think I'm gonna roll by for a couple hrs |
| 08:14:31 | jlivni_: | try hit up the afternoon |
| 08:15:42 | zanberdo: | jlivni_: I'd love a change to meet some of those I've seen/worked with here on IRC. I'm here today ( In the 300's atm ) and would like to find others in the PostGIS community to chat with. |
| 08:17:26 | zanberdo: | jlivni_: I will be here all day. I hope to run into you. I will be looking for the postgres conference ( though it's not listed in the show guide )... |
| 08:17:58 | jlivni_: | yeah - I'll be heading to the postgres conf in the afternoon. |
| 08:18:21 | zanberdo: | do you happen to know where that will be held? |
| 08:18:50 | jlivni: | no - i have no idea. only just noticed the conf is at moscone center - thats all the research ive done so far : ) |
| 08:20:02 | zanberdo: | heh... well, I will check it out at the postgres booth. It's not listed in the show guide, so I'm not exactly sure where it might be... but the pg guys ought to know. |
| 08:34:27 | darkblue_B: | zanberdo: yes, I'll be there for the PostGIS talk and after |
| 08:34:36 | darkblue_B: | meeting at noon |
| 08:34:54 | darkblue_B: | I'm tall, pony tail, brown hair |
| 08:35:10 | zanberdo: | darkblue_B: excellent. I'm not sure where the meeting will be held, as there is no mention of it in the show guide.. but I fancy I can ask the pg booth jockeys |
| 08:35:29 | darkblue_B: | I am not sure the booths are open yet |
| 08:35:34 | zanberdo: | 10:00am |
| 08:35:36 | darkblue_B: | I dont know where it is either |
| 08:35:42 | darkblue_B: | ohh ok |
| 08:35:53 | zanberdo: | I'm across the street outside the 300 rooms atm |
| 08:36:03 | zanberdo: | killing time... |
| 08:36:04 | zanberdo: | heh |
| 08:36:12 | darkblue_B: | !!! |
| 08:36:18 | darkblue_B: | mobile IRC then |
| 08:36:46 | zanberdo: | well, as mobile as my Lenovo T42 is... : ) Hotspots all over the place and it's quite over here in the 300's... |
| 08:36:47 | darkblue_B: | well I have some queries I've built up.. exercising things I have around.. I'd lke to expand that |
| 08:36:58 | darkblue_B: | ohh laptop |
| 08:37:24 | darkblue_B: | what i'd like to do is move the $DATA dir on a PG install.. actually put in 8.3.. |
| 08:37:28 | zanberdo: | I'll be interested in talking to another real, live postgis app developer |
| 08:37:37 | darkblue_B: | sure |
| 08:37:48 | zanberdo: | ah... you talking about migrating from 8.x to 8.3? |
| 08:37:52 | darkblue_B: | jlivni is the sharpshooter.. but I know a few tricks |
| 08:38:01 | zanberdo: | He'll be here too |
| 08:38:11 | darkblue_B: | watch out though.. he bites |
| 08:38:32 | zanberdo: | meh... I'm used to it... you have to have a thick skin to get into anything linux related as a noob. |
| 08:38:36 | darkblue_B: | keep your hands close to your body! |
| 08:38:55 | darkblue_B: | I'm not used to that culture... |
| 08:39:28 | darkblue_B: | hmm.. I could pg_dump -Fc all the DBs I have now |
| 08:39:33 | darkblue_B: | its a remote machine |
| 08:39:37 | zanberdo: | my, I'm just a hacker... for instance, when I migrated from 8.1 to 8.2 and later to 8.3, I simply created a backup from the previous version, ran it through sed to change the 8.x reference, then imported from cli... I'm sure that is *not* the official ( or problably the best ) way to migrate... |
| 08:39:53 | darkblue_B: | backup.. how? |
| 08:40:08 | zanberdo: | err... I use pgadmin3 |
| 08:40:17 | : | * zanberdo no good gui... heh : ) |
| 08:40:18 | darkblue_B: | ok.. I have pgadmin3 |
| 08:40:25 | darkblue_B: | hey I write GUIs! |
| 08:40:36 | darkblue_B: | I am not CLI tied |
| 08:40:36 | zanberdo: | heh... use em all the time, but I prefer cli where I can |
| 08:40:56 | darkblue_B: | I definitely prefer GUIs .. but they have to work! |
| 08:41:16 | darkblue_B: | I've cant run pgadmin3 as postgres on Linux |
| 08:41:24 | darkblue_B: | at least, I havent figured out how |
| 08:41:37 | darkblue_B: | so its a song and dance about the owner and privs |
| 08:41:40 | zanberdo: | yes, just use pgadmin3 to back up the database. don't compress it. It will create a sql script with all your data. Check it for direct reference to 8.x and use sed to convert to 8.3 ( is this is your plan )... |
| 08:41:54 | darkblue_B: | you have to go through every TABLE and give privs to another user |
| 08:42:12 | darkblue_B: | zanberdo: that does not sound good |
| 08:42:31 | darkblue_B: | I think pg_dump -Fc on each DB.. and the official script is better |
| 08:42:38 | zanberdo: | I didn't say it was right... now good! : ) Just that it worked. |
| 08:42:47 | darkblue_B: | sure.. |
| 08:42:50 | zanberdo: | s/now/nor/ |
| 08:43:20 | darkblue_B: | the tech of DBs or Data Integration seems to be just so |
| 08:43:25 | darkblue_B: | sloppy is bad |
| 08:43:26 | zanberdo: | I only discovered that I need to do that when I attempted to restore the backup into 8.2 from 8.1 and it failed when it found reference to 8.1... |
| 08:43:42 | darkblue_B: | well, yeah.. PostGIS has a lot of ways to fail |
| 08:43:46 | darkblue_B: | when porting |
| 08:43:54 | darkblue_B: | well, enough for me |
| 08:43:55 | darkblue_B: | :P |
| 08:44:01 | zanberdo: | at any rate, I was too lazy to read up on the proper migration path... so there it is. |
| 08:44:09 | zanberdo: | good luck |
| 08:44:49 | zanberdo: | will see you here. I'm the 6'0" "ginger" with short hair and a black and tan laptop backpack... |
| 08:45:13 | darkblue_B: | zanberdo: ok, gotcha |
| 08:45:25 | zanberdo: | cheers. Time to head back across the street. |
| 08:46:11 | zanberdo: | I will ask the pg guys the proper migration path... I'll need to know for my live servers.. ( did I mention I only used that method on my test server? I wouldn't do that on a live server... heh ) |
| 08:47:27 | TheSteveMonkey: | darkblue_B: ping |
| 08:47:40 | darkblue_B: | TheSteveMonkey: good morning |
| 08:47:53 | TheSteveMonkey: | did you get a chance to look at the latest rev of the slides |
| 08:47:57 | TheSteveMonkey: | I put them on slideshare |
| 08:48:05 | darkblue_B: | I'll look now |
| 08:48:13 | TheSteveMonkey: | oh yeah, and good morning to you too sir |
| 08:48:32 | TheSteveMonkey: | pramsey: I would appreciate if you could take a look as well and see if I made any glaring errors |
| 08:48:56 | pramsey: | what's teh url? |
| 08:49:31 | TheSteveMonkey: | http://www.slideshare.net/scitronpousty/using-post-gis-to-add-some-spatial-flavor-to-your-application/ |
| 08:49:32 | sigq: | Title: Using PostGIS To Add Some Spatial Flavor To Your Application ( at www.slideshare.net ) |
| 08:49:34 | TheSteveMonkey: | thx |
| 08:50:47 | pramsey: | sybase has geo? |
| 08:50:54 | pramsey: | DB2 and Informix is usually my IBM set |
| 08:51:07 | TheSteveMonkey: | pramsey you are correct - will fix now |
| 08:51:08 | TheSteveMonkey: | thanks |
| 08:51:14 | pramsey: | and of course, the SDE reference should note 9.3+ |
| 08:51:29 | TheSteveMonkey: | I could have sworn they did though |
| 08:51:39 | pramsey: | since this is a pgsql talk, you might note that ESRI actually wrote their own ST_GEOMETRY type for PsotgreSQL. |
| 08:51:42 | pramsey: | basically postgis-by-esri |
| 08:52:07 | pramsey: | the esri developer was on pgsql-hackers asking questions about index selectivity and so on |
| 08:52:34 | TheSteveMonkey: | I am not sure I am going to go that deep - but I may mention it in the talk |
| 08:52:45 | TheSteveMonkey: | Sybase does have spatial functiionality |
| 08:53:03 | TheSteveMonkey: | http://www.sybase.com/detail?id=1011212 |
| 08:53:04 | sigq: | Title: Certificate of Spatial Query Server ( SQS ) Certification Report: Database Management - Sybase Inc ( at www.sybase.com ) |
| 08:53:06 | pramsey: | cool. |
| 08:53:09 | pramsey: | so does ingres, btw |
| 08:53:15 | TheSteveMonkey: | true |
| 08:53:27 | pramsey: | ( since we're talking about undead databases : ) |
| 08:53:46 | TheSteveMonkey: | added them to the slide |
| 08:53:48 | darkblue_B: | lots of products trying to get on the bandwagon now I thnk |
| 08:54:00 | TheSteveMonkey: | I will re-upload once I finish the talk and get some feedback there |
| 08:54:31 | TheSteveMonkey: | there is money in them thar LBS functions |
| 08:54:31 | zanberdo: | lest I be thought a liar, I would like to state that I have ( finally ) found the reference to the pg converence on page 66 in the guide. |
| 08:54:36 | pramsey: | you should put "shapefile" in quotes, since presumably you're going to mention that it's a misnomer |
| 08:55:18 | TheSteveMonkey: | yeah - thats why I actually have the screen shot of the all the files that might be in a shapefile |
| 08:56:00 | pramsey: | udig will create .qix and .fix files too : ) |
| 08:56:12 | TheSteveMonkey: | I am about to go into the tunnel so I may lose my connection |
| 08:56:16 | TheSteveMonkey: | owrking on the train |
| 08:56:30 | TheSteveMonkey: | pramsey: are you doing more work on uDig |
| 08:56:41 | pramsey: | no, just spectating. |
| 08:56:43 | darkblue_B: | Clip to the Bounding Box has a Create TAble too |
| 08:56:56 | darkblue_B: | seems like too much going on in that slide |
| 08:57:37 | pramsey: | "Well Known Text", |
| 08:57:39 | pramsey: | not "Know" |
| 08:57:43 | pramsey: | slide 20 |
| 08:57:43 | TheSteveMonkey: | ok I will remove the create table |
| 08:58:08 | pramsey: | dunno if you want to ensure all your function references are the ST_ variants... |
| 08:58:10 | darkblue_B: | demo.decart.com/opensearchservices i think is cut off.. slide 42 |
| 08:58:14 | pramsey: | ( be "modern" and all that ) |
| 08:58:24 | pramsey: | ST_AsText( ), etc |
| 08:58:29 | darkblue_B: | also 43... |
| 08:59:18 | TheSteveMonkey: | what is wrong with 43? |
| 08:59:25 | darkblue_B: | urls clipped ? |
| 08:59:49 | TheSteveMonkey: | pramsey: should I make them all ST_Variants? Even stuff like sum? |
| 09:00:12 | pramsey: | no, sum( ) is a pgsql function |
| 09:00:24 | TheSteveMonkey: | duh |
| 09:00:29 | pramsey: | slide, you need to either reverse the arguments to ST_Within( ) or use ST_Contains( ) |
| 09:00:37 | pramsey: | slide 27 |
| 09:00:53 | TheSteveMonkey: | why? |
| 09:00:55 | TheSteveMonkey: | it works |
| 09:01:04 | TheSteveMonkey: | I am not saying I am right I just want to understand |
| 09:01:13 | darkblue_B: | Within and Contains are inverse equiv |
| 09:01:16 | darkblue_B: | sort of |
| 09:01:18 | darkblue_B: | :- ) |
| 09:01:26 | pramsey: | within( z,p ) is all the z's inside p |
| 09:01:31 | pramsey: | and you want all the p's inside z |
| 09:02:09 | darkblue_B: | well its not a clear case |
| 09:02:16 | darkblue_B: | I thnk you could ask wither quest |
| 09:02:20 | darkblue_B: | either |
| 09:02:33 | darkblue_B: | but, parcels are smaller I guess |
| 09:02:42 | pramsey: | hopefully |
| 09:03:09 | pramsey: | the current one will pick up all the parcels big enough to contain small zones |
| 09:03:10 | darkblue_B: | Points are unambiguous |
| 09:03:55 | TheSteveMonkey: | pramsey - thanks I think I was trying contains at first with a collect and all sorts of other fun stuff |
| 09:04:06 | TheSteveMonkey: | thanks for catching that. I need to reverse the order |
| 09:04:09 | pramsey: | np |
| 09:04:13 | darkblue_B: | TheSteveMonkey: are you going to be able to show the results of query somehow? |
| 09:04:37 | darkblue_B: | ( I used ogr2ogr and KML myself ) |
| 09:04:40 | pramsey: | slide 30, the "area" might be confusing to folks, but such is life in lon/lat |
| 09:04:55 | TheSteveMonkey: | pramsey: yeah it is confusing |
| 09:05:03 | darkblue_B: | yeah.. that seems odd to do an area Q in 4326 |
| 09:05:15 | TheSteveMonkey: | darkblue_B: I can since I have it all running on my laptop - that query takes forever though so I won't show that |
| 09:05:19 | darkblue_B: | you *do* explaing that |
| 09:05:28 | TheSteveMonkey: | yeah I do |
| 09:05:43 | darkblue_B: | I like pictures |
| 09:05:58 | darkblue_B: | so some kind of viusal result is importatnt I think |
| 09:06:03 | TheSteveMonkey: | I guess I could reproject on the fly to utm and meters or something but I think that might be over the top |
| 09:06:23 | TheSteveMonkey: | darkblue_B: I am planning on running QGIS at the same time and showing the results I already stored |
| 09:06:30 | TheSteveMonkey: | hence all the create table statements |
| 09:06:42 | darkblue_B: | QGIS sounds good |
| 09:07:05 | TheSteveMonkey: | I guess I need to redo that other query though |
| 09:07:13 | darkblue_B: | what the heck.. demo presence may be more important than to nit at the area question |
| 09:07:24 | darkblue_B: | this is an intro talk |
| 09:07:27 | TheSteveMonkey: | thanks so much for all the feedback you guys - this is great feedback |
| 09:07:44 | darkblue_B: | np |
| 09:07:47 | TheSteveMonkey: | exactly - my hope is just to get them pumped up about doing spatial stuff and then have them go read more |
| 09:07:56 | TheSteveMonkey: | all the other talks are postgresql stuff |
| 09:08:30 | darkblue_B: | well TheSteveMonkey.. David Fetter has an open slot at SFPug for an Advanced PostGIS some day |
| 09:08:47 | darkblue_B: | I just have to learn enough to gve that one! |
| 09:09:00 | TheSteveMonkey: | darkblue_B: I am not sure my kung fu is up to that |
| 09:09:05 | TheSteveMonkey: | I will be in attendance when you do |
| 09:09:09 | darkblue_B: | attendance was noticably good for my intro to PostGIS at SFPug |
| 09:09:20 | darkblue_B: | you may get a few people.. |
| 09:09:23 | TheSteveMonkey: | good |
| 09:09:24 | darkblue_B: | here today |
| 09:09:27 | TheSteveMonkey: | I hope I do |
| 09:09:33 | TheSteveMonkey: | are you at linux world now? |
| 09:09:42 | darkblue_B: | nope.. lunch meeting |
| 09:09:44 | TheSteveMonkey: | I am at the train station about to walk up |
| 09:09:46 | TheSteveMonkey: | ahhh |
| 09:09:50 | darkblue_B: | so I wont be there until your talk |
| 09:09:56 | TheSteveMonkey: | ok |
| 09:09:57 | darkblue_B: | oh well.. 3pm yeas? |
| 09:10:01 | TheSteveMonkey: | yeah |
| 09:10:06 | darkblue_B: | got it |
| 09:10:07 | TheSteveMonkey: | be kind to me : ) |
| 09:10:37 | pramsey: | have fun guys |
| 09:10:45 | TheSteveMonkey: | thanks pramsey |
| 09:10:53 | darkblue_B: | thx pramsey |
| 09:11:19 | TheSteveMonkey: | later guys - I am going to go check out the crowd |
| 09:11:21 | TheSteveMonkey: | bye |
| 12:25:25 | jlivni: | darkblue_B: http://postgis.refractions.net/pipermail/postgis-users/2008-August/020770.html |
| 12:25:26 | sigq: | Title: [postgis-users] How to trim a GeometryCollection to get aMultiPolygon? ( at postgis.refractions.net ) |
| 15:15:35 | iggy: | let's say I wanted to query for addresses around address1 and order the results by distance from address1, which function should I be looking at for the order by clause? |
| 15:16:03 | iggy: | I tried a few variations on this idea: |
| 15:16:05 | iggy: | select addr_cat, city, state, zip, zip4 from addresses where zip = '77006' and st_within( addr_point, st_expand( ST_SetSRID( ST_MakePoint( 29.741878 , -095.400069 ), 4326 ), 0.0011585807386 ) ) and home_prop_type in ( '', 'R', 'G', 'M', 'T' ) order by ST_Distance( ST_SetSRID( ST_MakePoint( 29.741878 , -095.400069 ), 4326 ), ST_SetSRID( ST_MakePoint( addresses.lat , addresses.lon ), 4326 ) ) ; |
| 15:17:14 | iggy: | but they all seem to give me errors |
| 15:31:07 | iggy: | hah |
| 15:31:28 | iggy: | order by ST_Distance( ST_SetSRID( ST_MakePoint( 29.741878 , -095.400069 ), 4326 ), addr_point ); |
| 15:31:36 | iggy: | works, and seems to be correct |
| 16:26:54 | springmeyer: | iggy: I use the bostongis nearest neighbor functions for distance stuff: http://postgis.refractions.net/support/wiki/index.php?Nearest%20Neighbor%20Search |
| 16:26:55 | sigq: | Title: PostGIS Wiki : Nearest Neighbor Search ( at postgis.refractions.net ) |
| 16:34:59 | iggy: | interesting |
| 16:35:52 | iggy: | springmeyer: how do you think that compare to the way I did it performance wise, etc? |
| 16:39:29 | iggy: | lots of good info on that bostongis.com site |
| 16:41:34 | springmeyer: | iggy: I can't say other than I'm sure the author would love to hear if you compare |