| 03:21:41 | CIA-6: | mcayland * r2799 /trunk/lwgeom/ ( 10 files ): Blast away the huge amounts of legacy C code related to PostgreSQL versions < 8.1. Next step will be to work on the SQL script files too. |
| 03:53:55 | CIA-6: | mcayland * r2800 /trunk/lwgeom/lwpostgis.sql.in.c: Remove all of the pre-PostgreSQL 7.3 schema #ifdefs from lwpostgis.sql.in to make things readable once again. |
| 04:10:19 | CIA-6: | mcayland * r2801 /trunk/loader/ ( Makefile.pgsql2shp.in PQunescapeBytea.c ): Remove PQunescapeBytea hack from pgsql2shp which is no longer needed, as it was only required for versions of PostgreSQL < 7.3 |
| 04:22:11 | CIA-6: | mcayland * r2802 /trunk/lwgeom/ ( long_xact.sql.in sqldefines.h.in ): Remove the HAS_SCHEMA #define and all related #else code since we now guarantee to be using PostgreSQL > 7.2 |
| 05:12:34 | CIA-6: | mcayland * r2803 /trunk/lwgeom/ ( long_xact.sql.in lwpostgis.sql.in.c sqlmm.sql.in ): Switch all SQL and PL/PGSQL stored procedures over to use dollar quoting instead of apostrophes for function bodies. At last, most of the stored procedures actually become readable |
| 07:08:51 | jalmeida: | is possible works to postgis and javascript |
| 07:09:28 | jalmeida: | renderer the features on browser( client ) from database |
| 07:09:42 | jalmeida: | ?? |
| 07:33:33 | CIA-6: | mcayland * r2804 /trunk/ ( configure.ac configure.in macros/ac_proj4_version.m4 ): Update copyrights for configure.ac ( rewritten from scratch, and now renamed from the deprecated form configure.in ) and also the PROJ.4 version detection macro |
| 07:43:57 | bitner: | jalmeida: you cannot work directly with the database from postgis, you must have some server side code sitting between the browser and the database like http://featureserver.org or http://geoserver.org |
| 07:43:58 | sigq: | Title: FeatureServer -- RESTful Geographic Feature Storage ( at featureserver.org ) |
| 07:44:39 | bitner: | if all you want to do is render, you could also render as an image using mapserver etc |
| 07:50:10 | CIA-6: | mcayland * r2805 /trunk/ ( 6 files in 2 dirs ): Update new Makefile copyrights, also remove compat.h from the loader/ directory since it is now no longer needed. |
| 07:51:41 | jalmeida: | do you have to example to work with openlayers and postgis ???? |
| 07:52:07 | CIA-6: | mcayland * r2806 /trunk/lwgeom/Makefile: Remove Makefile from lwgeom/ directory; it is no longer needed as it is automatically generated from lwgeom/Makefile.in |
| 07:52:15 | bitner: | featureserver and mapserver work very well between openlayers and postgis |
| 07:52:21 | jalmeida: | i create my database and want editing my featurers using openlayers, its is possible ??? |
| 07:52:33 | bitner: | with featureserver, yes |
| 07:52:34 | jalmeida: | bitner, = ) |
| 09:11:02 | CIA-6: | mcayland * r2807 /trunk/lwgeom/lwpostgis.sql.in.c: |
| 09:11:02 | CIA-6: | Some more updates to lwpostgis.sql.in.c; correct a couple of missing conversions |
| 09:11:02 | CIA-6: | from OPAQUE_TYPE to internal ( how on earth did regression tests pass with this |
| 09:11:02 | CIA-6: | still in place?? ), plus remove the UPDATE on pg_opclass to change the OID of the |
| 09:11:02 | CIA-6: | index storage type since we can do this by specifying a STORAGE clause instead. |
| 09:21:31 | pagameba: | hi - looking for ways to optimize my postgis experience with mapserver |
| 09:21:33 | pagameba: | I have the standard gist index on the_geom |
| 09:21:35 | pagameba: | my layers are classified on a field in the data - fcode |
| 09:21:39 | pagameba: | I have an index on fcode |
| 09:21:43 | pagameba: | its still somewhat slow |
| 09:22:41 | pagameba: | should I use a subselect in the DATA statement to limit the query ie the_geom from ( select * from mytable where fcode in ( 'val1', 'val2', 'val3' ) ) as foo using unique gid using srid=-1 |
| 09:23:09 | bitner: | pagameba: best way is to intercept the query and run it using explain analyze with psql or something like pgadmin |
| 09:23:37 | bitner: | I typically will do something like that |
| 09:24:01 | bitner: | using subselects that is |
| 09:24:17 | pagameba: | bitner thx |
| 09:24:39 | pagameba: | is it useful to explicitly set the fields to return as well instead of using *? |
| 09:25:09 | bitner: | I don't think that should matter unless there are a ton of fields |
| 09:25:27 | pagameba: | k |
| 09:26:58 | bitner: | the trick I use with mapserver is to use a pl/pgsql function that takes in data in %% notation in my mapfile and then build the correct query in pl/pgsql so that I can easily have optional fields and the like |
| 09:27:52 | : | * pagameba futzes with shp2img |
| 09:27:59 | bitner: | ie DATA the_geom from ( select * from myfunction( '%val1%','%val2%' ) )using ... |
| 09:47:00 | pagameba: | it does seem to improve things by using the subselect |
| 09:47:36 | bitner: | did you try explain analyze? |
| 09:48:57 | bitner: | that will tell you for sure how you are using your indexes |
| 09:50:07 | pagameba: | what is a bitmap heap scan? |
| 09:51:34 | bitner: | a lot of the bitmap useage came in to play at 8.2 |
| 09:51:49 | bitner: | main thing they allow is using multicolumn indexes out of order |
| 09:52:09 | bitner: | or making use of multiple separate indexes from one query |
| 09:52:13 | pagameba: | I only see it using one index |
| 09:53:52 | bitner: | are you just running the subquery or are you running the actual query that mapserver is calling? |
| 09:54:01 | pagameba: | both |
| 09:54:08 | pagameba: | the subquery doesn't seem to use any index |
| 09:54:21 | pagameba: | it is just doing a seq scan |
| 09:54:25 | pagameba: | with a filter |
| 09:54:53 | bitner: | what if you just explain the subquery? does that use the index on fcode? |
| 09:55:05 | pagameba: | no |
| 09:55:11 | pagameba: | select the_geom, fcode, gid from dtb_hydro_polygon where fcode in ( 'WASW','WARVLK','WARVDL','WALK' ) |
| 09:55:29 | pagameba: | does a seq scan |
| 09:55:57 | pagameba: | I have a btree index on fcode |
| 09:56:05 | bitner: | you have index on fcode right? have you vacuum analyzed the index? |
| 09:57:09 | : | * pagameba does that |
| 09:57:26 | bitner: | in psql or pgadmin query window or whatever run set enable_seq_scan to off; select the_geom...... |
| 09:57:48 | bitner: | to try to force the query planner to try to use index |
| 09:58:08 | bitner: | does fcode='WASW' rather than the in statement use index? |
| 09:58:39 | pagameba: | doesn't seem to change it |
| 09:59:07 | pagameba: | set enable_seq_scan to off |
| 09:59:17 | pagameba: | yields unrecognized configuration parameter |
| 09:59:53 | bitner: | drats, memory must be faulty |
| 10:00:04 | : | * bitner looks up what the actual statement is |
| 10:01:23 | bitner: | set enable_seqscan to off |
| 10:01:25 | bitner: | oops |
| 10:02:18 | pagameba: | ok, now it uses the index |
| 10:02:47 | bitner: | for some reason your planner does not think it is worthwhile to use the index |
| 10:03:12 | bitner: | is it faster when it uses the index? |
| 10:04:12 | pagameba: | difficult to say, but I think so ( marginally ) |
| 10:04:52 | : | * pagameba steps out for a meeting |
| 10:04:54 | pagameba: | thx bitner |
| 10:05:00 | bitner: | np, cheers |
| 11:36:07 | cgs_bob: | hello all. I am trying to find a way to display borehole data and it seems that I can use ST_MakeBox3D. Unfortunately the examples I've found ( such as ST_SetSRID( ST_MakeBox3D( ST_MakePoint( west_in,south_in ), ST_MakePoint( east_in,north_in ) ),4326 ) ) do not show a box with depth. anyone have any pointers? |
| 11:44:14 | bitner: | "do not show a box with depth" ?? |
| 11:44:30 | bitner: | you are not feeding makepoint any z values |
| 11:51:10 | cgs_bob: | bitner: I was looking at the docs to postgis and did not understand what LLB and URT in ST_MakeBox3D( <LLB>, <URT> ) is. I was just wishing to find examples on the net so I could figure out those arguments. |
| 11:51:39 | bitner: | Lower Left Bottom, Upper Right Top |
| 11:53:22 | bitner: | do you have your boring data in the db as points? |
| 11:54:15 | cgs_bob: | thanks. now I'd like to make a suggestion for the docs....a table of abbreviations and glossary : ) |
| 11:56:55 | cgs_bob: | bitner: I have 25000 boreholes in a database with ground water depth, lithology, sample analysis, and various other geotechnical data. btw, a lot of this data is also available over the internet |
| 12:02:00 | cgs_bob: | location of the borehole is in sample_location's top_lat,top_lon and the lithology has top_depth, bottom_depth for each layer |
| 21:17:07 | chris123: | greets |
| 21:18:17 | chris123: | currently setting up postgis with postgresql and I keep getting the same error when I enter the following command |
| 21:18:26 | chris123: | psql -d gis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql |
| 21:19:06 | chris123: | the error is as follows: psql:/usr/share/postgresql-8.3-postgis/lwpostgis.sql:1557: ERROR: |
| 21:19:20 | chris123: | any insight on how I can track this one down?? |
| 21:22:21 | chris123: | tks its late so need some sleep, will try again tomorrow |
| 21:22:22 | chris123: | ciao |
| 21:34:22 | simplexio: | chris123: did you do createlang plpgsql ? |
| 21:59:46 | chris123: | <simplexio> yes |
| 22:26:21 | cgs_bob: | hello all. I'm looking for a program that would allow you to create a postgis query and display the results without having to create new tables. is there such a beast? |
| 22:49:24 | springmeyer: | cgs_bob: what do you mean? are you trying to continuously write over a table? |
| 22:52:38 | cgs_bob: | springmeyer: I'm just looking for a program that would make it easy for me to learn postgis. for instance, in qgis, I can create a query for a certain feature, but you can't do joins. |
| 22:53:49 | springmeyer: | ah, i see... ya, so you are not writing queries 'by hand' in pure sql, you are interacting with postgis through qgis... |
| 22:53:53 | springmeyer: | hmm... |
| 22:54:23 | springmeyer: | well, particularly to joins in qgis... I think there is a plugin now that will do them |
| 22:54:39 | : | * springmeyer digs around to verify |
| 22:55:36 | cgs_bob: | cool...I'll look for it too |
| 22:57:24 | nhv: | wow can't you just make layers from queries ?? |
| 22:57:36 | springmeyer: | http://www.geog.uvic.ca/spar/carson/ |
| 22:57:37 | sigq: | Title: SPAR! Spatial Pattern Analysis & Research Lab -- Carson Farmer ( at www.geog.uvic.ca ) |
| 22:58:41 | springmeyer: | in terms of learning postgis... cgs_bob you'll really benefit from spending some time getting to know how to write basic select queries using the psql interpreter |
| 23:01:49 | cgs_bob: | springmeyer: I have been working on a query that would allow me to look at my borehole database, but the was getting weird results. so, I thought I'd look for tools that would make it easy to play with postgis |
| 23:02:10 | springmeyer: | ah, sounds good |
| 23:13:35 | springmeyer: | cgs_bob: whats your data format in general? |
| 23:18:11 | cgs_bob: | springmeyer: I have a table that has the location of each borehole along with other info about that borehole. I also have a lithology table that has a boreholeid, top_depth,bottom_depth,lith_type, and strat_unit_name. I'm trying to find a way to visualize this. |
| 23:19:37 | springmeyer: | so, you've got latitude / longitude in your table... or you've imported into postgis and the data is stored in the standard wkb? |
| 23:21:49 | springmeyer: | and what format do you store it in outside of postgis? |
| 23:23:16 | springmeyer: | seems like if your boreholes are 3d then you're on the right track storing your data in postgis since postgis supports Z values, but you'll need to serve your data out to a 3D client |
| 23:23:31 | springmeyer: | why not Google Earth? |
| 23:24:57 | cgs_bob: | the first table I mentioned has a geometry table and the lithology table has z values associated with the different layers of the earth. I'm hoping to make a linestring feature that I will eventually put into a shapefile |
| 23:27:01 | cgs_bob: | I work for a state agency and I have been able to convince my boss to take a look at FOSS. he wants to see what I can do first |
| 23:28:00 | cgs_bob: | I can't load software on my work computer, so I have to give him the results in a form that can be imported into our gis |
| 23:28:58 | nhv: | what is your gis ?? |
| 23:30:04 | nhv: | or what formats does it ingest ? |
| 23:32:15 | : | * nhv 's first job as a 'coder' was visualizing the boreholes in Alberta |
| 23:32:49 | springmeyer: | nhv sounds like your man |
| 23:33:30 | : | * nhv is not a QGis pro though |
| 23:33:36 | : | * springmeyer having not worked with boreholes wonders what cgs_bob wants to visualize... |
| 23:33:48 | : | * nhv too |
| 23:34:00 | : | * springmeyer is headed out for the night however |
| 23:34:21 | cgs_bob: | we have Intergraph MGE and have plans to migrate to Geomedia Professional. Unfortunately MGE uses a proprietary file format but Geomedia can import shapefiles |
| 23:34:45 | nhv: | can you export shapefiles ? |
| 23:36:11 | nhv: | or better can you make a 'SQL View' of your data |
| 23:37:37 | cgs_bob: | one other thing I have to say. We use to use a software package called ERMA Site Geologist that allows you to visualize boreholes. we can't use it any more because our gis is obsolete, so we are looking for other options. |
| 23:40:02 | cgs_bob: | I have all of the tables in postgresql now. |
| 23:40:22 | cgs_bob: | anyways, I got to get going...toddler woke up : ) |