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