| 04:01:56 | CIA-6: | robe * r2834 /trunk/doc/reference.xml: Provide examples of using ST_MakePolygon in conjunction with ST_Accum and PostgreSQL ARRAY( ) function |
| 05:31:06 | CIA-6: | robe * r2835 /trunk/doc/reference.xml: numerous changes( remove additional parent, argument list changes, multi function breakout ) |
| 10:07:05 | landonf: | Howdy. What do most people do with the shp.xml meta-data? I was considering using 8.3's support for XML to just drop the whole thing into a table, one row per imported shapefile. |
| 10:30:07 | milovanderlinden: | Hi there, I need some conceptual help: a potential client is looking at postGIS as the database system for their Location based services. They have a database containing address-interpolation on street-level ( fromleft,toleft and so on ) of the entire world ( where available ). They are pretty sure they are going to need a cluster with replication to get maximum uptime. Is it a good thing to offer them a solution with pgpool and plony? Or |
| 10:32:36 | landonf: | Probably a better question for #postgresql, but |
| 10:33:06 | landonf: | If the idea is to ensure uptime, a "warm" secondary copy using WAL shipping is probably going to be the easiest to set up and maintain. |
| 10:34:36 | landonf: | ( eg, using skytools ) |
| 10:35:10 | landonf: | Live replication is a pain in the neck and carries with it all sorts of caveats. |
| 10:39:35 | milovanderlinden: | landonf: ok! thank you for the tip. I will digg into the skytools documentation. |
| 11:21:54 | landonf: | So over the course of stopping by every few weeks, I think that I've come to the conclusion that this channel exists for lurking, not talking. |
| 11:22:53 | landonf: | Which is a bummer -- learning this stuff, informal conversation can go a lot further than reading the books on my own. |
| 11:32:11 | bitner: | landonf: it ebbs and flows |
| 11:33:52 | arkygeek: | hi all |
| 11:34:36 | arkygeek: | hoping someone can help shed some light on this: |
| 11:34:45 | arkygeek: | jadis=# AddGeometryColumn( 'Sites','the_geom',srid?,'POINT',2 ); |
| 11:34:45 | arkygeek: | ERROR: syntax error at or near "AddGeometryColumn" |
| 11:35:08 | arkygeek: | I am working on some notes i took almost two years ago... |
| 11:35:33 | landonf: | +select |
| 11:35:50 | arkygeek: | urfff |
| 11:36:32 | arkygeek: | why do i have srid? written down.... |
| 11:36:39 | arkygeek: | that seems a bit odd doesn't it? |
| 11:37:10 | landonf: | Well you need to specify an actual SRID, yeah. |
| 11:37:26 | arkygeek: | I have UTM WGS84 zone 36N as being 32636 |
| 11:37:30 | arkygeek: | trying that |
| 11:38:54 | arkygeek: | cool |
| 11:38:55 | : | * milovanderlinden feels sorry he is still in the process of lurking, even tough on the mapserver channel he is an active contributor and even maintainer for the mailinglist :-( |
| 11:39:15 | arkygeek: | thx landonf :- ) |
| 11:43:06 | milovanderlinden: | landonf: I agree with you that sometimes it is hard to stay motivated when all that happens is people lurking. Worse even; I recently found out that large consultancy firms in the netherlands are using open source a lot without contributing even a single penny. I made it my new life-mission to make sure these large firms start contributing in whatever way.. |
| 11:44:05 | milovanderlinden: | So Logica, Accenture, GE.. beware :D |
| 11:44:46 | bitner: | landonf: i'd like to point out that "stopping by every few weeks" is not the way to create an active irc channel :- ) |
| 11:45:01 | arkygeek: | my notes say: once the column is added, do: `update jadis set the_geom=GeomFromText( 'POINT( '||utm_x||' '||utm_y||' )',32636 );` |
| 11:45:04 | landonf: | bitner = ) |
| 11:45:27 | bitner: | part of the problem is that a lot of people stop by when they have problems, but then don't "lurk" when others have questions |
| 11:45:28 | arkygeek: | but this gives me an error... ( syntax error at or near "`" ) |
| 11:45:41 | : | * arkygeek raises his hand |
| 11:45:56 | : | * milovanderlinden grabs that hand |
| 11:46:04 | bitner: | arkygeek: you'd likely have better luck with makepoint( ) |
| 11:46:08 | bitner: | than geomfromtext |
| 11:46:42 | bitner: | update jadis set the_geom=setsrid( makepoint( utm_x,utm_y ),32636 ); |
| 11:47:27 | arkygeek: | bitner: ok. I have a table I imported from access. its about 10k records and has coordinates for archaeological sites in 3 different projections |
| 11:47:36 | arkygeek: | do i need the ` ? |
| 11:48:01 | bitner: | no |
| 11:48:39 | landonf: | Curious -- how'd you export the Access data? |
| 11:48:41 | arkygeek: | ok, got it... |
| 11:48:53 | arkygeek: | pgAdmin II |
| 11:49:01 | arkygeek: | older version... |
| 11:49:15 | milovanderlinden: | landonf: I just answered my first question on the postgresql channel! Are you proud of me? ( fishing for compliments ) |
| 11:49:25 | arkygeek: | x==easting y==northing ? |
| 11:49:31 | landonf: | heh, milovanderlinden++ |
| 11:49:42 | arkygeek: | actually, i guess i did too heh |
| 11:50:09 | arkygeek: | on the postgis channel... |
| 11:50:15 | arkygeek: | lol ( not really i guess :P ) |
| 11:50:26 | landonf: | arkygeek: Ah. I ask because I got stuck with a quite of bit of data regularly updated from municipalities, stuck in Access databases. Threw this together to handle regular conversion to something more usable: http://code.google.com/p/mdb-sqlite/ |
| 11:50:27 | sigq: | Title: mdb-sqlite - Google Code ( at code.google.com ) |
| 11:51:00 | arkygeek: | landonf: cool |
| 11:51:53 | arkygeek: | jadis=# update jadis set the_geom=GeomFromText( 'POINT( '||utmeast||' '||utmnorth||' )',32636 ); |
| 11:51:53 | arkygeek: | UPDATE 10403 |
| 11:51:57 | arkygeek: | :-D |
| 11:55:25 | arkygeek: | oh oh |
| 11:55:31 | arkygeek: | i think i screwed up |
| 11:55:57 | arkygeek: | not all the utm coordinates in jadis are utm 36 :-( ( |
| 12:04:36 | milovanderlinden: | arkygeek: nice.... a table with mixed zones |
| 12:04:42 | milovanderlinden: | poor you |
| 12:04:50 | arkygeek: | heh |
| 12:05:00 | arkygeek: | perhaps I should.... hrmmmm |
| 12:05:13 | arkygeek: | actually, i am not sure what to do really |
| 12:09:12 | milovanderlinden: | ?? astext |
| 12:17:59 | milovanderlinden: | arkygeek: you could try to put the utm zone EPSG code in a new collumn in the original table and then do: GeomFromText( 'POINT( '||utmeast||' '||utmnorth||' )',that_column );? |
| 12:18:48 | milovanderlinden: | make sure the target table is not restricted to what srid it uses. |
| 12:19:28 | milovanderlinden: | perhaps you need to split the tables up; then convert the contents to wgs84 and stitch the two together in a new table |
| 12:26:14 | bitner: | arkygeek: do you have something telling you what srid each record is? |
| 12:26:53 | arkygeek: | bitner: weel, i have a utmZone field |
| 12:27:32 | bitner: | are they all wgs84 north |
| 12:27:36 | bitner: | ? |
| 12:27:45 | arkygeek: | yes |
| 12:28:37 | milovanderlinden: | arkygeek: http://spatialreference.org/ allows you to search epsg codes |
| 12:28:38 | sigq: | Title: Home -- Spatial Reference ( at spatialreference.org ) |
| 12:28:40 | bitner: | update jadis set the_geom=setsrid( makepoint( utmeast,utmnorth ),32600+zonefield ) |
| 12:29:07 | bitner: | update jadis set the_geom=setsrid( makepoint( utmeast,utmnorth ),32600+utmZone ) |
| 12:29:16 | bitner: | as long as utmZone is just an integer |
| 12:29:22 | bitner: | like 36 |
| 12:30:54 | arkygeek: | it is |
| 12:31:15 | arkygeek: | oh i see what you did! |
| 12:31:17 | arkygeek: | cool |
| 12:31:35 | bitner: | work smarter, not harder |
| 12:32:33 | arkygeek: | bitner: that is cool. except i think i have constraints on srid |
| 12:33:06 | bitner: | update jadis set the_geom=transform( setsrid( makepoint( utmeast,utmnorth ),32600+utmZone ),4326 ); |
| 12:33:09 | bitner: | or something like that |
| 12:33:14 | bitner: | or drop the constraint |
| 12:33:29 | bitner: | if you are not using some other software that requires it against that table |
| 12:34:37 | arkygeek: | bitner: ok, in that case i will ask your advice. |
| 12:34:43 | arkygeek: | bitner: this is what i am trying to do |
| 12:35:38 | arkygeek: | bitner: i am doing network analysis between contemporaneous sites. i am using qgis and GRASS ( mostly grass ) |
| 12:35:58 | arkygeek: | all of my work so far has been in ut36 |
| 12:36:02 | arkygeek: | utm36 |
| 12:36:46 | landonf: | So my question is, what does everyone do with shp.xml files? |
| 12:37:07 | bitner: | landonf: leave it with the shapefile :- ) |
| 12:37:37 | landonf: | heh, but what about correlating loaded data with the metadata? Do you maintain some sort of other central store for the original data sources? |
| 12:38:19 | arkygeek: | how much error do you think there would be if i projected stuff from zone 37 iusing 36? |
| 12:38:42 | bitner: | landonf: I rely on my sometimes rusty memory and similar naming conventions -- not ideal, but you asked what do you do |
| 12:38:46 | arkygeek: | i work in jordan and israel |
| 12:38:58 | landonf: | bitner: Indeed I did! = ) |
| 12:39:44 | bitner: | arkygeek: I would talk to a geographer who works in that area to find a common projection that is suitable across that study area |
| 12:39:46 | landonf: | I'm trying to decide between modifying shp2pgsql to support loading the shp.xml into specific table ( schema, tablename, metadata ), or just writing a wrapper script we'll use locally. |
| 12:40:19 | landonf: | I'm not sure if it's something useful enough to warrant a patch and push upstream |
| 12:40:28 | landonf: | generically useful enough, that is. |
| 12:41:49 | bitner: | as long as it was an optional flag on shp2pgsql, I would think it could be useful |
| 12:42:32 | bitner: | I would just do it as a separate process though myself |
| 12:43:02 | bitner: | but obviously that type of linkage between the database and the metadata is not that critical to me |
| 12:43:38 | bitner: | arkygeek: when you say "network analysis" what do you mean? |
| 12:44:45 | arkygeek: | bitner: i am looking at interaction potential between them based on sustenance requirements and food production capability |
| 12:45:38 | landonf: | bitner: *nod*. Thanks for your input. |
| 12:45:44 | landonf: | Yeesh it's late. Lunchtime. |
| 12:50:35 | milovanderlinden: | arkygeek: the UTM projections are projected; revert them to a geographical coordinate-sytem like wgs84 and then select a projection coordinate system that suits the entire study area. Store them in a geographic projection, not a projected one would be my strong advice |
| 12:51:59 | bitner: | milovanderlinden: if you can find a projected system that is suitable for the entire area at the level of accuracy that you need, it is very advantageous to store data in a projected system |
| 12:52:24 | bitner: | especially since postgis is just using euclidean geometry |
| 12:52:51 | milovanderlinden: | bitner: Yes, but coming from two different projections it would be best to first merge them in a geographic projection |
| 12:53:16 | bitner: | why not merge them straight to a common projected srid? |
| 12:53:21 | milovanderlinden: | bitner: postgis may be, but how about the transforming libraries underneath? ( ogr/geos? ) |
| 12:53:58 | milovanderlinden: | bitner: you would loose accuracy. |
| 12:54:52 | bitner: | how would utm35 -> somesrid lose more accuracy than utm35 -> geog -> somesrid ? |
| 12:55:00 | milovanderlinden: | not |
| 12:55:11 | milovanderlinden: | but utm35 + utm 36 -> somesrid would |
| 12:55:38 | bitner: | how????? you are doing the transform individually on each record |
| 12:56:11 | arkygeek: | to be honest, almost every site i am interested in looking at is in zone36n |
| 12:56:25 | : | * jlivni senses a miscommunication... |
| 12:56:31 | : | * bitner too |
| 12:56:35 | arkygeek: | the rest are very very close. i think it will be ok |
| 12:56:56 | jlivni: | arkygeek: is your zone37 stuff 'near the edge' of the zone 36 definition? cuz you can cross over a little bit from the recommended zone and still be fine |
| 12:57:04 | arkygeek: | it is |
| 12:57:06 | : | * milovanderlinden cries because bitner is right when it concerns point objects |
| 12:57:14 | jlivni: | but you cannot use your 37 coords and pretend they're in 36 |
| 12:57:20 | jlivni: | you would need to transform them from 37 to 36 |
| 12:57:21 | : | * milovanderlinden and is not willing to take on the discussion about polygons and lines |
| 12:57:24 | jlivni: | and then do everything in 36 |
| 12:57:49 | : | * jlivni thinks bitner had a good point, even with polygons and lines |
| 12:58:04 | arkygeek: | look here: http://www.dmap.co.uk/utmworld.htm ( i study basicall the jordan valley... ) |
| 12:58:05 | sigq: | Title: DMAP: UTM Grid Zones of the World ( at www.dmap.co.uk ) |
| 12:58:10 | bitner: | update jadis set the_geom=transform( setsrid( makepoint( utmeast,utmnorth ),32600+utmZone ),32636 ); |
| 12:58:20 | jlivni: | what i mention above, tho, is really a bad idea if some of your 37 geoms are not _very close_ to the edge of the recommended area for z36 |
| 12:58:59 | arkygeek: | the command bitner gave me does transform them though... so i should be fine right? |
| 12:59:10 | : | * bitner wants to go back to jordan valley |
| 12:59:12 | jlivni: | yes - that is correct. |
| 12:59:31 | arkygeek: | bitner: you've been? cool. its great isnt it? |
| 12:59:38 | jlivni: | jordan looks mostly 37 to me .. i dont know where exactly the 'jordan valley' is |
| 12:59:45 | bitner: | arkygeek: just beware -- the further things fall outside of within zone 36, the further off calculations will be |
| 13:00:16 | arkygeek: | jlivni: border of jordan/israel |
| 13:00:41 | arkygeek: | eastern jordan == desert |
| 13:00:59 | bitner: | think dead sea |
| 13:01:01 | jlivni: | well, you should be ok. if you want to be super safe, you should probably use a projection that does well in that area, or maybe you can get away with storing geographic and doing distance_spheroid, etc ( i have no idea waht kind of analysis you are doing ) |
| 13:01:10 | : | * jlivni wants to go back to dead sea |
| 13:01:22 | : | * bitner too |
| 13:01:22 | arkygeek: | i think i should be ok |
| 13:01:41 | jlivni: | people with my last name are slightly less welcome on the eastside of that particular sea, however... |
| 13:01:44 | arkygeek: | it sort of spoils you for swimming _anywhere_ else in the owlrd though doesn't it :P |
| 13:02:21 | bitner: | arkygeek: are you in the area? if so find out what folks in the area use |
| 13:02:29 | arkygeek: | jlivni: what parr of canada are you in? |
| 13:02:44 | arkygeek: | bitner: i am in england |
| 13:02:52 | jlivni: | arkygeek: i'm in the san francisco USA part of canada ... : ) |
| 13:02:55 | arkygeek: | utm36 is pretty common... |
| 13:03:06 | arkygeek: | lol |
| 13:03:26 | arkygeek: | jlivni is n=josh@c-24-4-60-236.hsd1.ca.comcast.net i saw the .ca. part |
| 13:03:40 | arkygeek: | guess that would be california, and not canada lol |
| 13:03:43 | bitner: | ca=california where comcast is involved |
| 13:05:46 | arkygeek: | my two days in san fran consisted of freezing cold, worst hangover of my leife, $5 hostel, and alcatraz ( with the hangover ) |
| 13:06:45 | bitner: | worst hangover at least hopefully means that only half the trip was miserable and the other half was rocking |
| 13:06:57 | arkygeek: | :P this is true heh |
| 13:07:42 | bitner: | who was whining about this being a quiet channel earlier :- ) |
| 13:07:42 | mloskot: | bbgeos: status |
| 13:07:43 | bbgeos: | telascience-full: idle, last build 24442 secs ago: build successful |
| 13:07:43 | bbgeos: | telascience-quick: idle, last build 343866 secs ago: build successful |
| 13:07:43 | bbgeos: | telascience-stable: idle |
| 13:07:52 | mloskot: | bbgeos: force build telascience-stable |
| 13:08:09 | arkygeek: | the table has no column suitable for use as a key. Qgis requires that the table either has a column of type int4 with a unique constraint on it ( which includes the primary key ) or has a PostgreSQL oid colum |
| 13:08:48 | bitner: | add a column of type serial and make it the primary key |
| 13:09:20 | bitner: | I forget if postgres auto-fills in a serial column with values when you alter though, I think not |
| 13:11:11 | arkygeek: | i have a column siteid that is unique, but not sequential. i think thats what serial is? |
| 13:11:52 | bitner: | if it's unique, if you just make it the primary key, you should be fine |
| 13:12:05 | bitner: | is site id an integer? |
| 13:12:09 | arkygeek: | yes |
| 13:12:33 | bitner: | I *think* QGIS will be fine with it once you make it a primary key |
| 13:12:48 | : | * arkygeek googles how to make primary key |
| 13:13:04 | bitner: | pgadmin III makes it simple |
| 13:13:13 | bitner: | if you're lazy |
| 13:20:24 | arkygeek: | trying to use navicat |
| 13:24:07 | : | * arkygeek used thcli :P |
| 13:30:00 | bbgeos: | Details at http://buildbot.osgeo.org:8506/builders/telascience-stable/builds/0 |
| 13:30:01 | sigq: | Title: Buildbot: telascience-stable Build #0 ( at buildbot.osgeo.org:8506 ) |
| 13:32:32 | jlivni: | arkygeek: you mind me asking what type of data you're working with? |
| 13:32:55 | arkygeek: | can you be more specific jlivni? |
| 13:34:48 | jlivni: | oh .. you know .. location of old pots from ancient civlizations? planned expansions of large wall? |
| 13:35:01 | arkygeek: | oh, ok |
| 13:35:25 | jlivni: | lots of interesting gis work to be done there |
| 13:37:20 | arkygeek: | jlivni: well, i look at the archaeological record, and look at faunal remains to see what they were eating. then i look at population estimates, and calculate calorie requirements. with a calorie target, i can make a crop/animal production target, and with that, i can calculate an area requirement target, which i then identify in conjuction with landuse classification maps and walking time cost surfaces |
| 13:38:33 | arkygeek: | jlivni: if you are interested, i could bore you for hours! ;- ) |
| 13:39:04 | jlivni: | actually it sounds really interesting. i dont suppose you're going to foss4g this year? |
| 13:41:01 | arkygeek: | i wish ... |
| 13:41:22 | arkygeek: | i have 3 kids and am in lst year of my phd |
| 13:41:26 | arkygeek: | ( aka broke ) |
| 13:41:43 | arkygeek: | /s/lst/last |
| 13:42:00 | jlivni: | that'll definitely slow you down from a south africa trip. well too bad - i love chatting with folks who do the kind of analysis i assume you're doing |
| 13:42:44 | arkygeek: | jlivni: see pm |