| 00:13:15 | simplexio: | smartnude: no knowledge how it should be don, but if you need to find closest polygon by distance from one to many, i would try buffer + intersect |
| 00:15:30 | smartnude: | I tried both, they give _almost_ the same result ; ) |
| 00:26:00 | simplexio: | : ) |
| 00:26:07 | simplexio: | so ehich one is right |
| 00:28:01 | simplexio: | somewhere is my memoery is st_closestpoint or something |
| 03:39:06 | CIA-34: | mloskot * r4114 /spike/wktraster/scripts/pixval.py: [wktraster] Added support for querying pixel values of overview to pixval.py. |
| 03:53:48 | smartnude: | simplexio: okey.. I'll see what happens with that one! |
| 04:51:40 | CIA-34: | mloskot * r4115 /spike/wktraster/scripts/pixval.py: [wktraster] Minor fixes to pixval.py script. |
| 08:10:57 | : | * lasma is away: Gone away for now |
| 08:38:56 | CIA-34: | mloskot * r4116 /spike/wktraster/scripts/gdal2wktraster.py: [wktraster] Enable gdal2wktraster to dump all available overviews, not only first one as it was fixed for testing. |
| 09:29:49 | CIA-34: | mloskot * r4117 /spike/wktraster/scripts/gdal2wktraster.py: [wktraster] Added new option -M, --vacuum to issue VACUUM FULL ANALYZE command for each generated table at the end of data loading. Print basic summary at the end of rasters / overviews processing. |
| 11:23:59 | CIA-34: | pracine * r4118 /spike/wktraster/test/regress/rt_georeference.sql: |
| 11:23:59 | CIA-34: | [wktraster] Fixed ticket 150. We now store GDAL style georeference information: X & Y of upper left CORNER of upper left pixel instead of the CENTER of the upper left pixel. |
| 11:23:59 | CIA-34: | Also renamed RT_GeoReference( ) to RT_GdalGeoTransform( ) and added RT_ESRIWorldFile( ). |
| 11:24:32 | CIA-34: | pracine * r4119 /spike/wktraster/scripts/window.py: |
| 11:24:32 | CIA-34: | [wktraster] Fixed ticket 150. We now store GDAL style georeference information: X & Y of upper left CORNER of upper left pixel instead of the CENTER of the upper left pixel. |
| 11:24:32 | CIA-34: | Also renamed RT_GeoReference( ) to RT_GdalGeoTransform( ) and added RT_ESRIWorldFile( ). |
| 11:28:07 | CIA-34: | pracine * r4120 /spike/wktraster/scripts/gdal2wktraster.py: |
| 11:28:07 | CIA-34: | [wktraster] Fixed ticket 150. We now store GDAL style georeference information: X & Y of upper left CORNER of upper left pixel instead of the CENTER of the upper left pixel. |
| 11:28:07 | CIA-34: | Also renamed RT_GeoReference( ) to RT_GdalGeoTransform( ) and added RT_ESRIWorldFile( ). |
| 11:29:36 | CIA-34: | pracine * r4121 /spike/wktraster/rt_pg/ ( rt_pg.c rtpostgis.sql.in.c ): |
| 11:29:36 | CIA-34: | [wktraster] Fixed ticket 150. We now store GDAL style georeference information: X & Y of upper left CORNER of upper left pixel instead of the CENTER of the upper left pixel. |
| 11:29:36 | CIA-34: | Also renamed RT_GeoReference( ) to RT_GdalGeoTransform( ) and added RT_ESRIWorldFile( ). |
| 11:29:52 | CIA-34: | pracine * r4122 /spike/wktraster/rt_core/ ( rt_api.c rt_api.h ): |
| 11:29:52 | CIA-34: | [wktraster] Fixed ticket 150. We now store GDAL style georeference information: X & Y of upper left CORNER of upper left pixel instead of the CENTER of the upper left pixel. |
| 11:29:52 | CIA-34: | Also renamed RT_GeoReference( ) to RT_GdalGeoTransform( ) and added RT_ESRIWorldFile( ). |
| 11:30:02 | CIA-34: | pracine * r4123 /spike/wktraster/doc/RFC2-WellKnownBinaryFormat: |
| 11:30:03 | CIA-34: | [wktraster] Fixed ticket 150. We now store GDAL style georeference information: X & Y of upper left CORNER of upper left pixel instead of the CENTER of the upper left pixel. |
| 11:30:03 | CIA-34: | Also renamed RT_GeoReference( ) to RT_GdalGeoTransform( ) and added RT_ESRIWorldFile( ). |
| 13:25:17 | marco_: | please, helpme, whats is the url for the repository of postgis |
| 14:24:03 | CIA-34: | pracine * r4124 /spike/wktraster/rt_pg/rtpostgis.sql.in.c: [wktraster] Fixed ticket 151. Changed rt_ prefixes to st_ prefix to ensure seemless integration with geometry functions. |
| 14:24:24 | CIA-34: | pracine * r4125 /spike/wktraster/scripts/gdal2wktraster.py: [wktraster] Fixed ticket 151. Changed rt_ prefixes to st_ prefix to ensure seemless integration with geometry functions. |
| 14:24:40 | CIA-34: | pracine * r4126 /spike/wktraster/scripts/rtreader.py: [wktraster] Fixed ticket 151. Changed rt_ prefixes to st_ prefix to ensure seemless integration with geometry functions. |
| 14:26:40 | CIA-34: | pracine * r4127 /spike/wktraster/test/regress/ ( 32 files ): [wktraster] Fixed ticket 151. Changed rt_ prefixes to st_ prefix to ensure seemless integration with geometry functions. |
| 14:27:35 | CIA-34: | pracine * r4128 /spike/wktraster/test/regress/ ( create_rt_box2d_test.sql create_rt_gist_test.sql ): [wktraster] Fixed ticket 151. Changed rt_ prefixes to st_ prefix to ensure seemless integration with geometry functions and fixed test according to new storage of X and Y. |
| 15:02:34 | ha1331_: | how do I insert point to postgis column that is WGS84 but the data is form gps device that "uses" NMEA 0183 |
| 15:11:29 | pramsey: | parse the GLL record and note that the format of the numbers is DDMM.MMM |
| 15:21:36 | ha1331_: | pramsey: what would this translate into: 6015.5503,N,02452.3123 |
| 15:21:40 | ha1331_: | E |
| 15:22:30 | pramsey: | latitude = 60 + 15.5504/60, lon = 24 + 52.3123/60 |
| 15:23:06 | pramsey: | select ST_AsText( ST_MakePoint( 24 + 52.3123/60, 60 + 15.5504/60 ) ) in postgis terms |
| 15:23:21 | pramsey: | you'll need to case statement to N/E/S/W to get the signs right |
| 15:23:27 | pramsey: | yours happen to both be positive |
| 15:23:52 | pramsey: | btw, does that make sense? that point is near the arctic circle? |
| 15:24:41 | ha1331_: | pramsey: I know this sounds totally retarded but could you give me an exmple of insert statement. Ican figure out the N/E S/W thing on my php thingy |
| 15:24:58 | ha1331_: | nope, near helsinki, capital city of Finland :D |
| 15:25:22 | pramsey: | no, that's right, 60 deg north |
| 15:25:24 | pramsey: | ok, good |
| 15:25:39 | jlivni: | hal331_: insert into tablename ( id,the_geom ) values ( 1,ST_MakePoint( 24 + 52.3123/60, 60 + 15.5504/60 ) ) |
| 15:25:46 | pramsey: | ( I'm just wrong about where the arctic circle is ) |
| 15:27:03 | ha1331_: | jlivni: that works when I set the_geom to be WGS84? |
| 15:27:11 | jlivni: | ah right. make that: ...st_setsrid( st_makepoint..../60 ),4326 ) ) ( im assuming it's epsg:4326 ) |
| 15:27:33 | ha1331_: | jlivni: jeah 4326 sound what I used |
| 15:27:39 | ha1331_: | sounds |
| 15:28:32 | ha1331_: | sweet, I have like 4000 rows of data with points that actually arent on earth :D |
| 15:29:16 | ha1331_: | when I figure this out, I'll make a tutorial for other morons like me... |
| 15:35:41 | ha1331_: | jlivni and pramsey: thank's now it actually makes sense. Thanks a million, could actually give you million if I had it ( bet you guys hear it all the time ) |
| 15:40:00 | magic_hat: | Hi all. I'm having my first go with postGIS... I have a table with lat/lng records. I need to set the table up so that I can do queries that pull up the closest couple of records to a given point... any guidance, or link to an appropriate tutorial, would be appreciated. |
| 15:45:31 | : | * pramsey sighs at lat/lon. |
| 15:46:07 | magic_hat: | uh... ok. |
| 15:48:20 | pramsey: | it's a coordinate system thing. |
| 15:48:36 | pramsey: | lat/lon are spherical coordinates |
| 15:48:53 | pramsey: | and presumably you aren't looking for points within "0.001 degrees" since that's somewhat meaningless |
| 15:48:59 | pramsey: | to people on the ground |
| 15:49:06 | pramsey: | where we think in cartesian units |
| 15:49:10 | pramsey: | like meters and feet and miles |
| 15:49:28 | pramsey: | so getting the answer to your question is surprisingly complex |
| 15:49:46 | : | * pramsey feels like this deserves a FAQ entry on the wiki |
| 15:51:03 | magic_hat: | okay, you're definitely losing me here. Most of the GIS work I've done in the past has involved points that are denoted by lat/lon |
| 15:51:38 | pramsey: | right. now, plot those on a piece fo graph paper |
| 15:51:58 | pramsey: | and think about how you would figure out many miles a given point is from the center of the paper |
| 15:52:37 | magic_hat: | which, of course, would be wrong, because the earth isn't flat... which is what projections are all about, yes? |
| 15:52:43 | pramsey: | sqrt( a^2 + b^2 ) ? |
| 15:52:53 | pramsey: | right |
| 15:53:14 | pramsey: | if you put your data into a cartesian system ( a projection ) then the math all starts to work |
| 15:53:56 | pramsey: | here's a query that will work, but won't perform as your table grows: |
| 15:54:34 | pramsey: | select * from mytable where st_distance_sphere( mytable.geom, st_makepoint( inputlon, inputlat ) ) < myradius; |
| 15:54:55 | pramsey: | it dodges the coordinate system problem by performing the distance test against a spherical system |
| 15:55:18 | magic_hat: | I'm not anti-coordinate system, though. that's what the SRID value in AddGeometryColumn is for, right? |
| 15:55:28 | pramsey: | but unfortunately the indexing in postgis is cartesian so there's no index use in that query |
| 15:55:40 | pramsey: | right |
| 15:55:55 | pramsey: | if you have a small enough region of interest, there is probably a good cartesian system for you to work in |
| 15:56:45 | magic_hat: | Yeah, these are all addresses in Chicago, as geocoded by google. I think they're NAD83 or something. |
| 15:57:36 | pramsey: | OK, chicago is in UTM zone 16 ( utm being a common projection ) |
| 15:57:45 | pramsey: | so you could use SRID 26916 |
| 15:58:06 | magic_hat: | so... create a geometry column? run a query to create points from the coords? and then a query to sort by distance? I'm a bit at a loss for what that syntax would look like. |
| 15:58:36 | pramsey: | your insert statements might look like "insert into mytable ( id, geom ) values ( 5, st_transform( st_setsrid( st_makepoint( -80,45 ),4326 ),26916 ) ); |
| 15:58:44 | pramsey: | and then your query statement would be |
| 15:59:24 | pramsey: | select * from mytable where st_dwithin( mytable.geom, st_transform( st_setsrid( st_makepoint( lon, lat ),4326 ),26916 ),myradius ); |
| 15:59:39 | pramsey: | and that will perform as your table grows, because you can put a spatial index under it |
| 15:59:50 | pramsey: | create index mytable_gix on mytable using gist ( geom ); |
| 16:00:10 | magic_hat: | okay, that makes sense. |
| 16:00:16 | : | * pramsey phews |
| 16:00:42 | : | * pramsey copies this socratic log for later. |
| 16:00:54 | jlivni: | someone, make a tutorial out of the last 50 lines |
| 16:01:19 | magic_hat: | right now the AddGeometryColumn is returning "no matching function". what do I need to do to postGis-enable the table. Lol. another tutorial. Maybe I'll post it when I'm done. |
| 16:01:43 | : | * pramsey hands to jlivni so he can finish his doco and get home for dinner : ) |
| 16:01:50 | jlivni: | magic_hat: that should be reasonably well documented already : ) |
| 16:01:53 | jlivni: | heh, ill find you a link |
| 16:02:38 | jlivni: | http://postgis.refractions.net/documentation/manual-1.3/ch02.html |
| 16:02:39 | sigq: | Title: Chapter 2. Installation ( at postgis.refractions.net ) |
| 16:02:43 | jlivni: | start at step 7 of 2.2 |
| 16:02:51 | magic_hat: | Amen. thx. |
| 16:02:51 | jlivni: | ( assuming you've installed postgis ) |
| 16:04:09 | dassouki: | there were numerous examples on the refractions website that i can't find on teh wiki no more :( |
| 16:06:15 | magic_hat: | I'm guessing my type for the addGeometryColumn should be POINT? |
| 16:48:13 | magic_hat: | all right, all... still wrestling with turning my lat/lng fields to geometry: update items set the_geom=ST_PointFromText( 'POINT( items.lng items.lat )' ); / ERROR: parse error - invalid geometry |
| 16:48:17 | magic_hat: | Thoughts? |
| 17:07:17 | ha1331_: | I have now managed to insert postgis points how do I query them in a manner tha the resultset would have column for lon and column for lat? |
| 17:18:22 | jlivni: | ha1331_: st_x( geom ), st_y( geom ) |
| 17:18:25 | springmeyer: | Select ST_X( the_geom ), ST_Y( the_geom ) from your table; |
| 17:18:28 | springmeyer: | crap |
| 17:18:49 | ha1331_: | hehe, nice timing |
| 17:18:50 | springmeyer: | : ) |
| 17:19:53 | ha1331_: | is "GeomFromText( 'POINT( $gpsData->lon $gpsData->lat )', 4326 )" good way of inserting points? |
| 17:21:44 | jlivni: | ha1331_: sure, that works |
| 17:22:06 | ha1331_: | jlivni: not slow or slower than some other way? |
| 17:23:20 | jlivni: | st_makepoint is faster i think |
| 17:24:04 | ha1331_: | jlivni: is there something wrong with using that? By wrong I mean, is there downside for using that? |
| 17:24:18 | ha1331_: | st_makepoint I mean |
| 17:24:25 | jlivni: | depends. unlikely. |
| 17:24:46 | jlivni: | for postgis, no |
| 17:26:13 | ha1331_: | If I have let's say HP proliant with 8 cores, 10G ram and 4x intel SSD E-series in raid 1+0, how many updates per second should I expect it to be capable of handling? |
| 17:26:34 | ha1331_: | by updates, I mean inserts... me and my thinking |
| 17:26:57 | ha1331_: | would 1500 in a minute be too much? |
| 17:27:48 | jlivni: | depends; unlikely |
| 17:28:01 | : | * jlivni is one handed now : ) |
| 17:28:28 | ha1331_: | jlivni: you lost it, or just using it for something else? |
| 17:28:51 | ha1331_: | if so, not sure I'm ready or old enough to now what you use it for |
| 17:28:53 | jlivni: | baby on shoulder |
| 17:29:18 | ha1331_: | oh, that's nice. |
| 17:29:30 | ha1331_: | also like how you multi task |
| 17:33:58 | ha1331_: | does raid 1+0 affect on the io-operations per second? I'am aware that it increases the throughput, but how does it affect the iops? |
| 17:51:16 | jlivni: | ha1331_: probably better channels than this for that questions |
| 17:52:08 | jlivni: | but as far as fast inserts go, things like how many indices you have on your table, if you're comitting after every insert, and so forth, will probably have more impact than raid 1+0 ( vs whatever you want to compare it to ) |
| 17:52:12 | ha1331_: | jlivni: agree, not that much discussion going here, so I just said what was in my mind. Actually reading about it as we speak. |