WHERE NOT IN and NULL in Postgresql

NULL values trip up the WHERE NOT IN condition in Postgres. To fix this behavior, use a conditional (not null) in the subquery.

source: http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/

Duplicate OSM Features in UD Campus Map

I noticed a couple places where we have two polygons on top of each other (particularly for parking polygons) in the UD Campus Map. I just wanted to note, if you're trying to modify an existing polygon on the map, make sure that you're changing the existing shape, rather than adding a new one (otherwise we're loosing attributes on the exisiting shape).

Here are two screenshots to demonstrate what I'm talking about (note that only the second polygon retains attribute information/properties as listed in the right-hand sidebar), though it appears the first polygon was intended to replace the second.

I've fixed this problem by adding nodes to the second polygon (the original one that has attributes/properties) and dragging them to match the footprint of the first (new, intended to replace the first), and deleting that one.

Zoom to extent of new vector layer in OL

Zooming to the maximum extent of all features in a vector layer does not work unless all features have been loaded. Therefore it is necessary to register an event handler, as demonstrated below ('layer' is any object of class OpenLayers.Layer.Vector):

layer.events.register('featuresadded', map,function(){this.zoomToExtent(layer.getDataExtent())});

Random Feature Selection

I met with a gentleman today who needed to select random streets in Wilmington in order to do a field survey of urban forestry there. Here are the steps we took to do so:

-Use Excel and the RANDBETWEEN function to generate random numbers in a range = total number of features (in this case, streets in Wilmington). Drag this equation down a column = number of features in sample. Once this has been done, save/export (I usually like to use DBF for ArcGIS)

RANDBETWEEN(bottom,top)

Bottom is the smallest integer RANDBETWEEN will return.

Top is the largest integer RANDBETWEEN will return.

- with data added to ArcGIS, use "Frequency" tool to check that no duplicate values were generated

- do a regular one to one join, dropping out all values that don't match. The result will be a random set of features -- in this case, street segments.

View KML Source from MyMaps

Google MyMaps allows you to export KML by using the "Export to Google Earth" feature. If you copy/paste the URL that this button links to you get a KML, but it is a network reference to the actual KML. If you copy/paste the URL referenced in this KML in your browser, you'll get a warning "{errorText:"Unable to contact server."}". To get past this warning, you must decode the encoded URL reference. That means getting rid of all the "amp;" strings.

For example, change:

http://maps.google.com/maps/ms?ie=UTF8&hl=en&vps=1&jsv=250a&oe=UTF8&msa=0&msid=113097578162956796020.00048fc123b72558b50ff&output=kml

to

http://maps.google.com/maps/ms?ie=UTF8&;hl=en&vps=1&jsv=250a&oe=UTF8&msa=0&msid=113097578162956796020.00048fc123b72558b50ff&output=kml

and you'll get the actual kml. You do not need to do this within any script or program, since those tend to encode URLs anyway. However, it is very useful for debugging issues with KML.

Geoserver Labeling, Font Changes

Wierd Geoserver Behavior (bug): font changed from Arial to some kind of serif font for no apparent reason. I fixed this by clearing all caches and restarting the Geoserver service. I'm still not sure what caused the issue.

Animal Movement and Home Range Options for ArcGIS

The Animal Movement Extension was written in Avenue, and development stopped many years ago. Therefore, Animal Movement Extension is not available for ArcGIS 9.x or 10.

There are a couple of options if you'd like to move forward with ArcGIS

• A lot of people using Animal Movement extension migrated to Hawths Tools, to be able to use ArcGIS. Hawths tools offered many of the functions in Animal Movement, though Hawths tools is also now a discontinued project, and will not work with newer versions of ArcGIS (9.2 was the last version it worked with).
• Hawths tools has been been merged into the Geospatial Modeling Environment, which works with ArcGIS 10. You can download this and find more info at: http://www.spatialecology.com/gme/
• Home Range Tools for ArcGIS works on ArcGIS 9.x, and is available at http://nhsbig.inhs.uiuc.edu/wes/home_range.html
• There is a lot of work going on with Agent Based modeling right now. I met with an agent based modeling working group at the ESRI conference. They've developed an extension for ArcGIS based on repast called Agent Analyst: http://www.institute.redlands.edu/agentanalyst/ . I didn't hear about any scenarios involving wildlife movement, buy there is a book coming out, and I would assume those scenarios might be discussed. I'll keep my ear to the ground on this one
• There are also contributed and native tools and scripts that could be used to perform many of these functions if strung together in the right order

Other Resources

• http://snr.unl.edu/powell/teaching/nres896/density_HR/nres896_links.htm
• http://warnercnr.colostate.edu/class_info/fw663/

Geoserver SLD Polygon Labels Repeat

Fix: had specified larger tile size for earlier debugging. Switch tilesize back to 256 to fix label repeat on polygons.

FIX! Geoserver not displaying labels

I've been having an extremely frustrating problem over the last month ... in one word: labeling. My latest labeling challenge, was that Geoserver refused to show labels for many buildings. I tried all kinds of things ... isolating the buildings layer, checking my query order, changing conflicts/overlap options ... pretty much anything I could think of to get labels to display.

Finally, I figured it out! There is an obscured parameter in older ('stable') versions of Geoserver, which was finally exposed through SLD vendorOption in the latest release of Geoserver/Geotools. This parameter MIN_GOODNESS_FIT is used when labeling polygons in particular. Based on MIN_GOODNESS_FIT geotools determines if From geoserver docs:

Geoserver will remove labels if they are a particularly bad fit for the geometry they are labeling.

The label is sampled approximately at every letter. The distance from these points to the polygon is determined and each sample votes based on how close it is to the polygon

so labels are removed if letters are beyond a certain threshold from the polygon. That meant that small buildings (especially ones with longish names) would never be labeled.

However, in Geoserver 2.1 the SLD VendorOption "goodnessOfFit" is exposed

Syntax follows this example:

0.3

the default value is 0.5, so anything lower will allow more lables to be placed.

For more, http://docs.geoserver.org/latest/en/user/styling/sld-reference/labeling.html

Issue discussion:

• http://jira.codehaus.org/browse/GEOT-1868
• http://jira.codehaus.org/browse/GEOT-2446?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel#issue-tabs

Geoserver WebTileCache

I was ultimately not able to deal with cutoff labels in Mapnik (or labels not being placed because of tile/overlap issues), so I went with Geoserver for the UD campus map project. The WebTileCache automatically tilecaches all services ... only challenge is to use SLD for styling.

Mapnik label cut off strategies

I've been spending a lot of time dealing with mapnik labels/shields being cut off. Here are some strategies:

1. change the "buffer_size" property of the map object in the mapnik xml ... you can also direclty change this in generate_tiles.py (which comes with osm mapnik package). I don't think the mapnik xml was being read in my case. When I changed the line self.m.buffer_size to = 0, my cutoff problems disappear
2. you'll need to do some tweaking with the order layers are defined within mapnik/cascadenik
3. also tweaking of text-allow-overlap and text-avoid-edges (these are cascadenik properties, there are similarly named ones for mapnik alone)
4. you may need to merge segments of the same road

To sum up: my strategy was first getting all my ordering straightened out with how layers are defined/ordered within mapnik/cascadenik. I then turned most layers to text-avoid-edges and text-allow-overlap to true. I set the buffer value to 0 in the .py file that I was using to render my tiles (in the osm package). Then moving around stuff that overlapped badly and will possibly need to merge segments of the same road.

ImportError: No module named mapnik

I'm still not sure what exactly causes this error: "ImportError: No module named mapnik" .. clearly Python is not finding the mapnik bindings, but why?  I think it might be related to a new install of ArcGIS, which installs a new version of Python.  I am also trying to figure out how exactly to fix this.  Recently I relaunched "cmd" and that worked.

python zipfile.py MemoryError

Was getting the following error on zipfile.py (actually while attempting to run a cascadenik script):

newdata = self.dc.decompress(newdata)

MemoryError

I fixed this error by changing the compression algorithm that my .zip was being created with to "STORE" (i.e. no compression).

python cgi with xxamp/apache on windows

I recently figured out that a web service class I was dealing with (WFS in OpenLayers) would only accept requests through a proxy script (despite the requests were sent to my local machine!). OpenLayers has a .cgi proxy script which runs through Python, and here's how I got it to work:

1. Download proxy.cgi to the cgi-bin folder within xampp (or wherever it is), and install Python. I used version 2.5
2. Follow these instructions to setup Apache for running python
3. Finally, make sure you change the "shebang" line at the top of proxy.cgi from #!/usr/bin/env python to #!c:/python25/python (if python.exe is installed at C:\Python25\python.exe)

Tilecaching MXD/ArcGIS Server to file/folder-based only (e.g. google, mapnik)

So it seems like there's no automated way to go from mxd to file/folder based (rather than web service) tile cache. The best I could do was use maptiler/gdal2tiles (maptiler is a very nice gui for gdal2tiles) to cache raster layers. This will fuse multiple raster layers, but doesn't handle vectors. If you want to pass vectors through here you can always convert to raster with some other tool but you'll end up loosing any scale dependencies. Please note, you'll likely have to incorporate a part of their auto-generated code for google maps, since the file naming doesn't completely conform to x/y/z google scheme naming. I've included my modified tile provider class for the gmap_overlays submodule (part of the gmap addons submod collection ... lives in PATH_TO_DRUPAL\sites\all\modules\gmap_addons\gmap_overlays\js), which handles output from maptiler/gdal2tiles.

note: exposing as wms and using tilecache might be a good idea, but this non-automated solution that is OK for me right now

AGS error: #000569: Failed to generate cache. All server contexts failed to cache map.

I was getting the following error when trying to cache a map service in ArcGIS Server/ArcCatalog: 000569: Failed to generate cache. All server contexts failed to cache map.

I ultimately resolved this issue by upgrading my server software (ArcGIS Desktop) to the same version as my client (in my case 9.3.1 service pack 1). Caching only within a feature, which in my case was a three county polygon that was much larger than the area I wanted to cache, greatly improved performance ... I'm not sure if it even would have worked without that.

Note: if postinstallation hangs while upgrading server software check out this post: http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=32766

Drupal SWFTools Error

Was getting the following error: "You are missing some Flash content that should appear here! Perhaps your browser cannot display it, or maybe it did not initialize correctly." on Chrome and I.E. only. Figured out that by changing the embed method under Embedding settings (Home>Administer>Site configuration>SWF Tools>Embedding settings) back to "Direct embedding - do not use JavaScript replacement" from " SWFObject 2 - JavaScript" I no longer get this error

Tile basemap through Views/GMaps module (Drupal)

To add a tiled basemap to a gmap created with views (and the gmaps) module:

1. First make sure that you have a gmap set up through views. This is a good tutorial: http://groups.drupal.org/node/19614

2. Next download the GMap Addons module, if you haven't already done so

3. Create a map tile structure using something like tilecache, mapcruncher, or ArcGIS (you can generate a map with the google maps scheme, just not sure where to get an xml that would adequately describe the scheme to ArcGIS)

4. Finally add a tile overlay macro within the style (GMap) configuration for this view. Use the following form:

[gmap |overlay=tile:http://host/tilesdirectory/{Z}/{X}/{Y}.png]

... notice you don't need to substitute anything for Z, X, or Y ... these correspond to folder numbering as generated with tilecache (or other tiling program) using the google maps scheme.

If you save the view you should now be able to see your basemap in a page generated by that view.

Migrating ArcIMS to a new machine

1. Install Apache (2.2.x, to c:\Apache2.2\, in my case) and Tomcat (6.0.x, to c:\Tomcat6.0\, in my case) make sure each is able to launch independently. You might need to install Java JDK and set environment variables JAVA_HOME and JRE_HOME to the JDK directory
2. Download mod_jk (1.2.28 httpd-2.2.3, in my case). Install by dragging mod_jk-xxx.so to the Apache modules folder and renaming mod_jk.so. Add this line to httpd.conf: LoadModule jk_module modules/mod_jk.so
3. Add a workers.properties file to the apache conf directory. This file sets options for your ajp (probably ajp13). Make sure it points to your JDK, Tomcat home directory, etc.
4. Install ArcIMS with the 'custom' option during post-install. Make sure that you select the JDK you already installed.
5. Copy over old aimsacl.xml from webapps\servlet\WEB-INF\classes. Give * permissions to all your apps for testing, you can change later if necessary
6. Copy over old old apps from the Tomcat webapps directory. Make sure WEB-INF\classes\Authenticate.properties is pointing to the new aimsacl.xml
7. Use AIMS administrator to set up map service, using similiar settings as old service. Note: on my install 'Output', 'Website', and 'Axl' are all under the Apache htdocs directory
8. Add all applicable webapps directories to httpd.conf as in the following (substituting 'example' for your own app):
JkMount /example ajp13
JkMount /example/* ajp13
9. Change any connection properties in your webapps to match new machine
10. Restart Apache, Tomcat, enjoy.

json_encode pg_fetch_array

For some reason you need to run the result of pg_fetch_array through a loop and store in another array to be able to encode as JSON ... here's an example:

// Connecting, selecting database
$dbconn = pg_connect("host=HOSTNAME dbname=DBNAME user=DBUSER password=PASSWORD")
or die('Could not connect: ' . pg_last_error());

// Performing SQL query
$query = "SELECT * FROM mytable";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

$resultArray = array();
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
$resultArray[] = $row;
}

echo json_encode($resultArray);

// Free resultset
pg_free_result($result);

// Closing connection
pg_close($dbconn);

Mapnik ... success!

Here's how I imported, rendered, and served OSM data through mapnik, locally:

1. Do all downloads/installs ... python (2.5 worked for me), postgres/postgis (8.3 worked), osm2pgsql, mapnik, rendering/mapnik utilities for OSM
2. Follow info below on osm2pgsql
3. Make sure mapnik bindings are registered with Python, run the demo under demo\python to make sure mapnik works, here are pretty good instructions from the OSM wiki
4. Follow this readme to run generate_xml.py and generate_tiles.py. Use my tips below if you get stuck ... also make sure you keep your coordinates straight ... these scripts won't throw an error, they'll just crash
5. Once you've generated tiles stick them in a web accessible location and use this tutorial to get them working with OpenLayers ... note the "bespoke" section, which shows how to add your custom tiles

... now to customize the rendering I only need to modify the map.xml file created by generate_xml.py ... I could do this manually or might look into the qgis plugin ... also cascadenik looks cool

OSM2PGSQL

After getting mapnik installed and running I realized:

1. generate_tiles/generate_xml (and the rest of the render utilities) are really necessaryfor rendering OSM through mapnik. Therefore, you must download both mapnik through the mapnik site but also the mapnik OSM rendering tools which you can get through SVN at http://svn.openstreetmap.org/applications/rendering/mapnik/
2. you must download world coastlines to get the osm/mapnik utilities to run as detailed here (if you're getting an error about 'world_boundaries', that's why)
3. you must use OSM2PGSQL to import your osm data into postgres ... you can do this through osmosis, but it won't work

Helpful hints with OSM2PGSQL

1. you must run the included .sql in order to create the proper spatial reference information for the projection mapnik likes
2. you must create the database with a postgis template
3. you might need to export through JSOM and encode as UTM8 ... I did this as a safeguard this time and it worked ... if you're having trouble, try this

Related errors:
failed: ERROR: AddGeometryColumns() - invalid SRID
CONTEXT: SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )"
PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement

relation "planet_osm_polygon" does not exist

Loading OSM with OSMOSIS

Now that I've decided to locally render OSM data for the campus, I need to get the OSM data for the maximum extent of campus areas into a local database (both Mapnik and I prefer Postgres). I initially tried doing this by using OSM2PGSQL, but that ultimately failed ... not completely sure why.

Here are the steps I took to load this data into Postgres using OSMOSIS:

Required installations: Postgres with Postgis, OSMOSIS ... tested environment: osmosis 0.32, postgres 8.3, Windows XP

1. Download desired extent from OSM using JOSM ... save the downloaded data as *.OSM
2. Convert this .osm file to UTM8 encoding (e.g. using notepad++)
3. Login to postgres, create a new database based on postgis template
4. Run pgsql_simple_schema_0.6.sql, which is located in the osmosis install location in the 'script' directory
5. Open a command line and run: PATH_TO_OSMOSIS_BATCH_FILE --read-xml file="PATH_TO_OSM_FILE" --write-pgsql host="HOST_NAME" database="DB_NAME" user="USER_NAME" password="PASSWORD", for example: C:\qgis_projects\render\osmosis-latest-bin\osmosis-0.32\bin\osmosis.bat --read-xml file="C:\qgis_projects\render\newark_josm_utm8.osm" --write-pgsql host="localhost" database="gis" user="postgres" password="mypassword"

Plugging away with OSM

Update: Success! Was able to convert to OSM with Export_to_OSM.py, all was needed was to change some paths in the code. Uploads via JOSM using the Open, and Merge commands (I recommend also adding some tag so you can query these out later if you need to do anything with them), then Upload. Watch as I add more layers on www.openstreetmap.org!

Old Post: It now seems I'll be able to use OpenStreetMaps either as the repository and rendering system for the full campus map, or as a selective baselayer for national data (for directions). The answer will depend, in a large part, on whether I am able to get campus layers into OSM.

I have worked with multiple OSM editors: JOSM, markaarter, and the QGIS OSM plugin and none will successfully take a shapefile, allow import into OSM format, and then upload to the OSM servers. I have tried conversion to KML and GPX, since these are formats accepted by one or the other of these editors ... still no dice.

There are scripts and programs which were specifically created to convert SHP (and all kinds of other formats) to OSM. I've checked out some of these programs osm2shp, shp-to-osm.jar even gml2osm ... problem is that they assume you want to map all the shapefile attributes ... seems like it could be a lot of extra work to figure these out for every layer I want to upload.

Installing QGIS with osgeo4w, express crashes

Installing QGIS with a standalone installer is definitely the way to go for someone with little time, no need for other osgeo4w libraries or features in newer ('unstable') releases.

However, as I need other osgeo4w packages as well as features in the 'unstable' release (unstable is somewhat of a misnomer, as I understand, really is just shorthand for saying it has features that aren't fully supported as in the 1.x version), I wanted to install with the osgeo4w package. For some reason osgeo4w crashes when I try to use the express install option, though I needed to use the advanced install anyway in order to get unstable qgis.

Steps for this are as follows

1. download osgeo4w from http://trac.osgeo.org/osgeo4w/
2. open installer, select advanced install
3. select the following packages to install: qgis (unstable if you want, which was 1.4 at the time I wrote this), qt4-libs, libpq, zlib, geos, gdal16, ogdi, expat, proj (you will need to rename, read this), xerces-c, hdf4, hdf5, libjpeg,libgeotiff,libpng,libtiff, netcdf,libjpeg12, sqllite3, tcltk, zlib1 (install zlib and make a duplicate renamed to zlib1.dll), curl, iconv, sip, pyqt4,
   

Basemap, prority renderers/services based on extent?

A new idea: use custom service/renderer within a given extent and cloud service outside of that extent ... tag campus layers accordingly, so they're picked up by custom renderer

Directions, directions

So I am returning in the new year with a major task at hand: finish work on UD campus maps. As I recall, the major loose end from where I left off was the matter of the basemap -- which resurrected old considerations about maintaining all layers on a cloud site (i.e. openstreetmaps) and send all operations to be dealt with on a local spatial database. During this diversion from the previous course I need to also keep in mind: the basemap issue only comes up because I want to offer national directions to campus in the same interface as the rest of the campus map ... otherwise I could just use local layers without need for larger extent basemap layers.

The OSM scenario is very appealing for a number of reasons:
-cloud hosting would free up local resources here
-the OSM platform is well designed according to collaborative logic, that would allow updates by data stewards on campus, if we were to move in that direction
-OSM is open source, it's free to us, and is very interoperable with other open software ... that would help in efforts to support open source on campus
-possibly better performance on the cloud
-use of existing renderers allows us to leverage existing cartographic/labeling rules

Disadvantages
-layers could be edited by a third party with poor editing skills, inaccurate data, or malicious intent
-limited control over appearance of the map
-strange issues with spatial referencing ... OSM and existing UD layers do not agree ... these are also off with navteq streets, so problem is independent of OSM ... should note that the issue is probably not significant without sidewalks, since these tend to be more closely located to streets than buildings to streets
-still unresolved data sensitivity issues (we need to contact all data "owners")
-tendency to neglect local resources ... no redundancy

These disadvantages could mostly be overcome, but would probably require quite a bit of work, with some of the advantages becoming mooted
-OSM data could be stored locally, taking advantage of some tag which signifies campus data ... that is what UMd does. However, that is probably impractical for the national dataset (UMd only has part of the DC region), and would moot any advantages to using the cloud ... would however help with redundancy
-A custom renderer could be used to achieve more control over appearance ... this may be unwieldy to setup ... could try to tweak tags, etc. in OSM data as a work around ... could Mapnik default rendering be adequate?
-spatial referencing issues are independent of OSM