Pozi Connect Configuration
PoziConnect provides a simple interface for accessing, processing, analysing and exporting spatial and non-spatial data.
It uses the power of the popular open source GDAL/OGR translator library, with these important enhancements:
- it provides a simple interface that exposes typical translation functions that administrators can preconfigure using INI files and enable users to select and adjust according to specific tasks
- it uses the bare minimum information required to complete a task; for example:
- it doesn’t care if a destination file or table already exists - it has the intelligence to create or overwrite as necessary
- it uses the specified source and destination file extensions to determine the required translation settings
- it provides access to Python and database functions within the configuration for operations like indexing and file operations
Running as a batch task
To run multiple Pozi Connect tasks sequentially and avoid having to open up Pozi Connect and pick and run individual tasks, you can configure a ‘recipe’ for Pozi Connect to follow. This is useful if you have a series of tasks you regularly need to run (for example, M1s) or for setting up as a scheduled task.
Here we will use the Melton M1 tasks as an example.
Create a text file…
PoziConnect\recipes\Melton M1.txt
…with the following text…
Melton\Melton M1 - 1 - Import Authority.ini
Melton\Melton M1 - 2 - Import Vicmap.ini
Melton\Melton M1 - 3 - Generate M1.ini
Then create a batch file or scheduled task with the following target:
PoziConnect.exe --recipe="recipes\Melton M1.txt"
(You may need to specify the full path to the exe file instead of just PoziConnect.exe
.)
This will launch Pozi Connect and run all the tasks specified in the recipe.
Configuration
Pozi Connect is controlled using INI files. You can edit these files in a text editor.
If using Windows Notepad, the line returns in existing files may not display correctly. Notepad++ is recommended for editing these files.
User Settings
Notes:
- If key name ends with ‘folder’, the interface offers the user a button to launch an Open Folder dialog.
- If key name ends with ‘file’, the interface offers the user a button to launch an Open File dialog
- If key name ends with ‘password’, the interface displays dummy characters in place of the populated or typed characters
Examples:
[User Settings]
Vicmap_Address_Folder:
Vicmap_Property_Folder:
LGA_Code: 302
Database_File: Output\Ballarat.sqlite
[User Settings]
Pathway_DSN: pthprod
Pathway_User_ID:
Pathway_Password:
Pathway_Table_Prefix: pthdbo.
General Settings
Example:
[General Settings]
Description:
Extract Pathway address and parcel
information into Pozi Connect database
Pathway_Connection: ODBC:DSN={Pathway_DSN};UID={Pathway_User_ID};PWD={Pathway_Password}
Database_File: Output\Ballarat.sqlite
Parameters
Source/Destination
Files
- SHP
- TAB
- CSV
- DXF
- DGN
- GML
- KML
- GPX
- VRT
- XLS, XLSX
- JSON, GeoJSON
- DBF
- MIF
- XML (GeoRSS)
Databases
Note: specify file path or connection, then comma, then table name
- SQLite (.sqlite, .db)
- PostGIS
- Oracle
- ODBC
A reference guide for the SQL functions in SQLite and Spatialite is here. A tutorial with examples is here.
Web Service
- WFS
Examples:
Source: C:\Temp\Road.tab
Source: C:\Temp\Vicmap.sqlite,Road
Destination: PG:host='server.pozi.com' port='5432' dbname='councilgis' user='opengeo' password='abc123',Road
Destination: OCI:gisadmin/abc123@127.0.0.1,Road
If
Notes:
- the execution of any section can be made conditional
- use a Python expression to return true or false
Example
If: os.path.exists('{Input_Folder}/ConquestRoads.TAB')
Select
Notes:
- Comma-delimited list of fields from input layer to copy to the new layer. A field is skipped if mentioned previously in the list even if the input layer has duplicate field names. (Defaults to all; any field is skipped if a subsequent field with same name is found.) Starting with OGR 2.0, geometry fields can also be specified in the list.
- equates to
-select
parameter in ogr2ogr
Example:
Select: ogc_fid as fid, prop_propnum as propnum, ezi_address as address, transform(ST_SimplifyPreserveTopology(transform(the_geom, 28355), 1),4326) as the_geom
Where
Notes:
- Attribute query (like SQL WHERE)
- equates to
-where
parameter in ogr2ogr
Examples:
Where: prop_propnum is not null and prop_propnum <> ''
Where: OGR_GEOMETRY='POINT'
SQL
Notes:
- SQL statement to execute. The resulting table/layer will be saved to the output.
- equates to
-sql
parameter in ogr2ogr
Examples:
SQL: SELECT * FROM pthdbo.cnacomp (NOLOCK)
SQL: select RURAL_NO, RDNAME, RDTYPE, PROPNUM, COMMENTS, PRIMARY as IS_PRIMARY, HOUSE, ID, RA_Complete, XCOORD, YCOORD, PROPERTYNA, DATE from Rural_Address_Original_GJ
SQL: select * from ADDRESS where lga_code = '{LGA_Code}'
SQLFile
Example:
SQLFile: {Shared_SQL_Folder}\M1 R Edits.sql
Native SQLite Processing
When performing operations where the source and destination are the same SQLite database, you can take advantage of SQLite’s native query processing rather than OGR’s. This may help for some complex queries.
Example:
SQLProcessing: SQLite
TransformCoordSys
Notes:
- reproject/transform to this SRS on output
- equates to
-t_srs
parameter in ogr2ogr
Example:
TransformCoordSys: EPSG:4326
AssignCoordSys
Notes:
- assign an output SRS
- equates to
-a_srs
parameter in ogr2ogr
Example:
AssignCoordSys: EPSG:28355
OverrideCoordSys
Notes:
- override source SRS
- equates to
-s_srs
parameter in ogr2ogr
Example:
OverrideCoordSys: EPSG:28355
GeometryType
Notes:
- Define the geometry type for the created layer. One of NONE, GEOMETRY, POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION, MULTIPOINT, MULTIPOLYGON or MULTILINESTRING.
- equates to
-nlt
parameter in ogr2ogr
Example:
GeometryType: None
Index
Example:
Index: status,tpklpatitl
SpatialIndex
Example:
SpatialIndex: No
Commands
Examples:
Command: startfile('{Output_Folder}\\')
Command: DSNList()
PostCommand: system('ogrinfo {Pathway_Connection}')
Command: startfile('output\PoziConnect.log')
SkipInfo
By default, Pozi Connect obtains information about a table and writes it to the log before importing the table’s contents. In some circumstatnces, this adds a significant load to the source server. Use SkipInfo
to prevent Pozi Connect from obtaining the table info.
Example:
SkipInfo: true
OGRInfoOnly
Special case used where usual source-destination is not relevant. For example, if updating an existing table.
Example:
OGRInfoOnly: true
Advanced
A number of other OGR commands are available for advanced use within Pozi Connect. These operate as per the ogr2ogr cli. Currently, the following commands are supported. For advanced usage, see the ogr2ogr documentation.
Dim
Defines the dimentions of the output layer. Supported values are XY, XYZ, XYM, and XYZM. layer_dim can also be specified to copy the dimention of the output layer.
Example:
Dim: XY
SPAT
Defines the spatial query extents for the source layer. Allows spatial filtering of the source features prior to processing.
Example:
SPAT: 0 0 1000 1000
SPAT_SRS
Defines the SRS of the spatial query extent, provided in SPAT
Example:
SPAT_SRS: EPSG:3111
GT
Defines the number of features grouped per transaction. Defaults to 20000. Changing this value may provide performance improvements for some data sources.
Example:
GT: 2000
ZFIELD
Defines the field of the source layer which will be written to the Z dimention of XYZ or XYZM layers.
Example:
ZFIELD: alt
CLIPSRC
Defines a layer/feature used to clip the source layer before processing. A bounding box, WKT Geomerty or a datasource can be provided to the tool.
Example:
CLIPSRC: d:\test.shp
CLIPSRCSQL
Defines the SQL to run against CLIPSRC to use in the clip.
Example:
CLIPSRCSQL: SELECT * FROM test where group = 1
CLIPSRCLAYER
Defines the named layer of the CLIPSRC to use in the clip.
Example:
CLIPSRCLAYER: Test
CLIPSRCWHERE
Defines an attribute query on the CLIPSRC to use in the clip.
Example:
CLIPSRCLAYER: Test
CLIPDST
As per CLIPSRC, except the clip operation is completed after data has been processed.
Example:
CLIPDST: d:\test.shp
CLIPDSTSQL
As per CLIPDSTSQL.
Example:
CLIPDSTSQL: SELECT * FROM test where group = 1
CLIPDSTLAYER
As per CLIPDSTLAYER.
Example:
CLIPDSTLAYER: Test
CLIPDSTWHERE
As per CLIPDSTWHERE.
Example:
CLIPDSTLAYER: Test
SIMPLIFY
Simplifies a complex layer, using an algorithm which preserves topology per feature, but not the layer. This option accepts a tollerance value.
Example:
SIMPLIFY: 5
SEGMENTIZE
Segmentizes a layer by adding nodes. This option accepts a maximum node distance value.
Example:
SEGMENTIZE: 10
EXPLODECOLLECTIONS
Exploding collections takes a multipart feature and breaks it into its single part components.
Example:
EXPLODECOLLECTIONS: True
MAPFIELDTYPE
Similar to SQL’s CAST, this function allows the change of field type. All={type} can be used to change all values to a particular type. Accepts Integer, Integer64, Real, String, Date, Time, DateTime and Binary.
Example:
MAPFIELDTYPE: ALL|String
ADDFIELDS
Similar to append, this function also add any new field in the source to the destination.
Example:
ADDFIELDS: True
RELAXEDFIELDNAMEMATCH
Relaxes the matching of field names between source and destination
Example:
RELAXEDFIELDNAMEMATCH: True
FORCENULLABLE
Do not propogate NOT NULL constraints between source and destination
Example:
FORCENULLABLE: True
UNSETDEFAULT
Do not propogate DEFAULT constraints between source and destination
Example:
UNSETDEFAULT: True
MO
Adds metadata to the destintion, if the layers supports it.
Example:
MO: True
Open & Creation Options
The following commands can have multiple options specified, separated by a pipe ‘ | ’ character |
- dsco
- lco
- doo
- oo
Example:
LCO: SHPT=POLYGONZ|RESIZE=YES
Usage
Import MapInfo TAB file into SQLite database
[Vicmap Address]
Source: {Vicmap_Address_Folder}\ADDRESS.tab
SQL: select * from ADDRESS where lga_code = '{LGA_Code}'
Destination: {Database_File},VMADD_ADDRESS
Index: property_pfi
Specify destination coordsys and geometry type
[Vicmap Features of Interest - Polygon]
Source: {Vicmap_Features_of_Interest_Folder}\FOI_POLYGON.tab
Destination: {Database_File},vmfeat_foi_polygon
GeometryType: MULTIPOLYGON
TransformCoordSys: EPSG:4326
Filter by geometry type during import
[Vicmap Features of Interest - Point]
Source: {Vicmap_Features_of_Interest_Folder}\FOI_POINT.tab
Where: OGR_GEOMETRY='POINT'
Destination: {Database_File},vmfeat_foi_point
Translate MapInfo table to CSV
[Vicmap Reference Table - VMADD_ACCESS_TYPE]
Source: {Vicmap_Reference_Folder}\ADDRESS_ACCESS_TYPE.tab
Destination: {Output_CSV_Folder}\VMADD_ACCESS_TYPE.csv
Import non-spatial MapInfo TAB file into SQLite database
[Vicmap Parcel-Property]
Source: {Vicmap_Property_Folder}\PARCEL_PROPERTY.tab
Destination: {Database_File},VMPROP_PARCEL_PROPERTY
GeometryType: NONE
SpatialIndex: NO
Index: parcel_pfi,property_pfi
Update an existing SQLite table
[Update PC_Council_Property_Address]
OGRInfoOnly: true
Destination: {Database_File},dummy
SQLFile: Tasks\Swan Hill\SQL\Swan Hill PC Council Rural Address.sql
Delete a table from a SQLite file
[Vicmap Vegetation - Remove Tree Density]
OGRInfoOnly: true
SQL: drop table vmveg_tree_density
Destination: {Database_File},dummy
Zip up output
[General Settings]
ZipFilePath: {Output_Folder}/PIQA Export.zip
ZipSession: ZipFile('{ZipFilePath}', 'a', ZIP_DEFLATED)
[PIQA Parcel Export]
Source: {PlaceLabDB}
SQLFile: {ParcelSQLFile}
Destination: {Output_Folder}/PIQA Parcel Export.CSV
PostCommand: {ZipSession}.write('{Destination}')
[PIQA Zip Finalisation]
Command: {ZipSession}.close()
[Display in Windows Explorer]
Command: startfile('{Output_Folder}\\')