Coldfusion uses inner db connection when nesting cfquery

When you implement a nested cfquery to different datasources, ColdFusion will use the inner connection for both queries. Symptoms are that tables can not be found: ERROR: relation "mapdef" does not exist

<cfquery datasource="vista_monitoring2" name="x">
select * from mapdef limit 1
<cfquery datasource="vista_wintermanagement_r" name="y">
select * from rides limit 1
</cfquery>
</cfquery>

oracle odbc very slow in ogr/mapserver

Today i converted an adress-table from mysql to oracle (9.2). The data is presented on a map using virtual spatial data over odbc. It appeared the oracle odbc connection was very slow (up to a minute for a couple of records). After a search on google i found some suggestions:
- enable tracing in odbc to see what data is send and recieved (it appeared all the oracle tables where investigated, since there are 100's, it takes a while)
- in this post http://osdir.com/ml/gis.mapserver.user/2004-07/msg00245.html JF presents a solution 4 this by adding the tablename to the DSN-definition in the ovf file
That helped, response time dimished to fractions of a second


ODBC:me/secret@db,table table
wkbPoint

ID

Install a routing mechanism on postgis (windows).

based on http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007

download and install
- postgres 8.2 (pgRouting is not yet available as binary for postgres 8.3)
- postgis (comes with postgres 8.2)
- pgRouting 1.0

C:
cd Program Files\PostgreSQL\8.2\bin

//create db
createdb -U postgres -E UNICODE routing
createlang -U postgres plpgsql routing

//load postgis and pgrouting into db
psql -U postgres -f ../share/contrib/lwpostgis.sql routing
psql -U postgres -f ../share/contrib/spatial_ref_sys.sql routing
psql -U postgres -f ../share/contrib/routing_core.sql routing
psql -U postgres -f ../share/contrib/routing_core_wrappers.sql routing

//load datafile generated witd shp2pgsql

//add columns
ALTER TABLE dummy ADD COLUMN source integer;
ALTER TABLE dummy ADD COLUMN target integer;
ALTER TABLE dummy ADD COLUMN length double precision;
UPDATE dummy SET length = length(the_geom);

//create network
SELECT assign_vertex_id('dummy', 0.001, 'the_geom', 'gid');

//optimise table
CREATE INDEX source_idx ON dummy(source);
CREATE INDEX target_idx ON dummy(target);
CREATE INDEX geom_idx ON dummy USING GIST(the_geom GIST_GEOMETRY_OPS);

//test network trace
SELECT gid, AsText(the_geom) AS the_geom FROM dijkstra_sp('dummy', 1, 100);

//update srid (option)
select updategeometrysrid('dummy', 'the_geom', 4326);
update dummy set the_geom = setsrid(the_geom,4326);

//finished! now check out http://boston.freemap.in/routing.html for implementation options!

Blogged with the Flock Browser

SRID stuff in PostGIS

Got these errors?

ERROR: Operation on mixed SRID geometries

ERROR: new row for relation "dummy" violates check constraint "enforce_srid_the_geom"

Be sure all your geometry tables share the same srid! Srid is defined in tabel geometry_columns. update srid in this table using this statement:

select updategeometrysrid('dummy', 'the_geom', 4326);

Next update the srid in the geometries itself using this statement:

update dummy set the_geom = setsrid(the_geom,4326);

check it using:

select getsrid(the_geom) from dummy

select asewkt(the_geom) from dummy limit 10

Map file export from qgis

In QGis (a desktop GIS client supporting esri-shape, postgis, wms...) you can export a project as a umn mapserver mapfile. When i tried that out yesterday, QGis 9.1 threw an error: map export error:ms_export phyton module not found. I tried replacing ms_export.py by a new one, but that didn't work out. Today i installed QGis 9.2 rc1. In QGis 9.2 ms_export is converted to a real plugin (amongst many other improvements). Also debugging is better, it still threw the same error, but the message was more descriptive! It appeared i forgot to select a project file, in the export-window. Export was succesful! http://guinea.genuchten.net

Blogged with the Flock Browser

CF/Oracle 8; expected NUMBER got DATE

Today i found this error in ColdFusion 7/Oracle 8

Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-00932: inconsistent datatypes: expected NUMBER got DATE

It appeared to be the fact that oracle 8 misunderstands <cfqueryparam cfsqltype="cf_sql_bigint"> as a date.

oracle import/export dumps

An easy way to port data from one oracle database to another is import/export (bundled with oracle client software). Open a dos-box/shell and type

exp user/pw@tnsname file=export_data.dmp tables=table1,table2,table3

Import the data again with:

imp user/pw@tnsname file=export_data.dmp fromuser=olduser touser=newuser

Remind:

• an export made by an user with dba-rights can't be imported by a user without dba-rights. Export the data with a user without dba-rights
• You can check a dump-file on having been exported by a dba-user by opening it in Textpath. On the second line the first character is a U for User or D for DBA.
• an oracle 9 client cannot export data from an oracle 10 database
• If importing geodata from a 9 to a 10 database, use SDO_MIGRATE.TO_CURRENT(tabname,column_name) to convert the geometries.

optimize mapserver performance

There are several ways to improve UMN-mapserver performance. These are a couple of them:

- Take care that mapserver only draws features which are visible and readable by setting proper scale-ranges for layers. If you have mapserver draw 1000s of features leaving you with a chaotic map which is onreadable, think again.

- For shapedata use the mapserver tool shptree to create a tree index for larger shapes. Mapserver uses the index to find the proper features to draw. The index is saved in shape.qix file. http://mapserver.gis.umn.edu/docs/reference/utilityreference/shptree. Very usefull is this batch file which indexes all shapes in a directory at once (save as xxx.bat and place in shape directory)

@ECHO OFF
set shptree=""
IF EXIST C:\ms4w\tools\mapserv\shptree.exe (
set shptree=C:\ms4w\tools\mapserv\shptree.exe
)

IF %shptree%=="" (
ECHO shptree.exe is missing.
GOTO:EOF
) ELSE (
GOTO loopDir
)

:loopDir
FOR /R %%A in (*.shp) do CALL :Subroutine %%A
GOTO:EOF

:Subroutine
ECHO Index: %1
%shptree% %1
GOTO:EOF

- For a set of rasters use gdaltindex (bundled with gdal/ms4w) to index the rasters into a single shape file.

- If you have a set of comparable shapes (like shapes per city in a district). Combine them into one layer using a tileindex (tile4ms tool). http://mapserver.gis.umn.edu/docs/reference/utilityreference/tile4ms. Tile4ms uses an input file with all the shape titles to combine. This input file can be generated with a dos-command

dir /b /s *.shp > metafile.txt

If you ever decide to move the shapes to another location. Remember to update the tileindex-dbf since it contains file references.

If you have a very large shape, it's sometimes faster to cut the shape in parts and combine the parts using tile4ms. Imaptools has created a tool, shp2tile, which can automatically slice a big shape into a set of tiles. Shp2tile as windows binary is included in ms4w.