adding google srid (900913) to postgis
I was loading some openstreetmap data into my postgis, when I noticed that postgis doesn’t include the unofficial google projection (900913). The official one is 3785, but osm2pgsql still uses 900913. Searched around a bit, and found following working insert statement:
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values (900913 ,'EPSG',900913,'GEOGCS["WGS 84", DATUM["World Geodetic System 1984", SPHEROID["WGS 84", 6378137.0, 298.257223563,AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]], NIT["degree",0.017453292519943295], AXIS["Longitude", EAST], AXIS["Latitude", NORTH],AUTHORITY["EPSG","4326"]], PROJECTION["Mercator_1SP"],PARAMETER["semi_minor", 6378137.0], PARAMETER["latitude_of_origin",0.0], PARAMETER["central_meridian", 0.0], PARAMETER["scale_factor",1.0], PARAMETER["false_easting", 0.0], PARAMETER["false_northing", 0.0],UNIT["m", 1.0], AXIS["x", EAST], AXIS["y", NORTH],AUTHORITY["EPSG","900913"]] |','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs');
heavy duty web gis server
Last week, I had a question regarding the hardware specs for a heavy duty web map server (based on GeoServer). This is of course no exact science, there are some things that need to be clear before you determine the right specs for a beefy web gis server.
The first thing there’s to know is the purpose of the server. Based on previous experiences, I can say that a WFS is much lighter on the CPU than a WMS (of course, a non tile-cached server). This is obvious, as WFS doesn’t need to render an image. But don’t under estimate WFS, creating an XML file of considerable size (> 5 mb), as result of a complex query can create a nice performance drain also.
Currently I don’t have any experience with WCS services, so I cannot give an advice on the load of a decent WCS, but I can do imagine that an on-the-fly reprojection of a 500 mb raster file will put a heavy workload on the cpu! 
The purpose of this particular server was to deliver tree types of services: WMS, WFS and WCS. The good news was that the WMS may be cached, and the front-end was openlayers, so tile caching is an option.
The next question you should ask, is the expected load of the server. How many concurrent users does it need to support, and how many requests in an hour should it handle. The answers to this weren’t very clear: approximately 5 concurrent users (for the WCS), but for the WMS definitely more (as it’s used as a background map service, up to 300 concurrent users). The WFS was somewhere in between…
The final question is of course the price, how much may this machine cost? This is the easiest question for everybody to answer!In this case the answer was 5000€
Ok, this basically means: a heavy machine, which have to be able to do on-the-fly heavy processing for the WCS, and needs allot of (fast) disk space for the caching of the WMS and the source data of the WCS. Next step for me was to go to the site of Dell, and configure a server machine for this task. This is my result:
A Dell PowerEdge R410 with:
2 Intel Xeon’s E5530 (so that’s a total of 8 cores)
16 Gb of RAM (DDR @ 1333 Mhz)
4 x 146 Gb Harddisk (15k rpm) in raid 5, which is ideal for cached services like a tilecache, also for a wcs)
Redundant power supply
Dual port Gigabit network (intel pro 1000pt)
This all for 5150€ (exl vat)
I hope this will be enough, as it’s the best fit hardware for the job and the amount of money I could find.
Also, this question reminded me that I should do some testing on how a WCS (and more exactly GeoServer) is performing. Anyone who has a spare machine laying around that I can use to perform these tests on?
Oh, and btw, the database has to be installed on the same machine.
ArcGIS Server REST Frustrations
Apparently ESRI has a hard time understanding how a cache works… In my experience, when you add a service (or application, or whatever) for the first time, there’s no cache of that service, right? It wasn’t there before, so there is no cache.
Now when you add or publish a service on ArcGIS Server (the 9.3.1), or if you make changes to the service, you need to clear the REST cache, in order to access the service through the REST API. That’s not logical?! When you add a service, you need to clear the cache?? Cache’s sole purpose is to speed up request, that are already made, but for new request?? Where has best practice gone??? Common ESRI, you can do better than that!!!
How to clear the cache of the REST service: go to
http://yourMachine/ArcGIS/rest/admin/
and clear your cache. You can also set an interval to clear your cache.
crossdomain requests with flex and arcgis server
Today, a colleague had a problem with setting up a demo of the flex api of ESRI. There was a certain layer (hosted on another ArcGIS Server), that wouldn’t want to show up. The same layer did show up in an JavaScript application, and the log files on the server didn’t mention of any connection made by the flex api, so the problem was not the server.
Because on the client side, everything seemed alright, I decided to fire up Firebug. It was the saver of the day! . Firebug told me that the flash application received a 404 (file not found) on the crossdomain.xml file on the server. Because it was the first time that I developed something with the flex api, or with flash, this crossdomain.xml was unknown for me. After some googling, I found that flash applications doesn’t allow cross domain request, unless the target server has this crossdomain.xml file in its root that allows the request from this particular server (btw, I found this info here [adobe.com]).
So to get this application working, I just needed to put this crossdomain.xml file, with the right permissions in the root of the server. In the case of ArcGIS Server, the necessary info can be found here.
This is how my crossdomain.xml file looks like, please note that this is a development server, so it accepts every request:
<?xml version=”1.0″?>
<!DOCTYPE cross-domain-policy SYSTEM “http://www.adobe.com/xml/dtds/cross-domain-policy.dtd”>
<cross-domain-policy>
<site-control permitted-cross-domain-policies=”all”/>
<allow-access-from domain=”*”/>
</cross-domain-policy>
process explorer
As a long time Linux user (SuSE – OpenSuse), I’ve never thought I would ever need this windows application: Process Explorer
This application shows you information about which handles and DLLs processes have opened or loaded. So you can see wich program has a particular file or directory open. Handy!
I needed this application to close a hanging java instance: I was uninstalling ESRI’s ArcGIS Server 9.3 Java edition, and it complained about a log file (derby.log) that was used and locked by another application. Of course, it didn’t mentioned which application, so Process Explorer To The Rescue! Just fire it up, and find the derby.log file. The application told me that is was being used by java.exe. So the only thing I needed to do was (in the command line) execute: taskkill /F /PID 00000 and that was it.
PostgreSQL Tips and Tricks
Couple of weeks ago, I wrote an article about writing PostGIS Spatial Queries (here).
Today I found the following post: PostgreSQL Tips & Tricks. There are a couple of tips for working with a PostgreSQL database. Here’s a summary:
- don’t do sequential scans, use indexes instead;
- index all foreign keys;
- denormalize can sometimes help;
- avoid joins;
- don’t do unachored text searches or full text searching;
- use the COPY command for bulk data loading;
- use multi-column indexes (I didn’t know this one…);
- don’t bother indexing evenly distributed booleans or enum columns;
- use EXPLAIN ANALYZE to benchmark and compare queries;
- use expression indexes (didn’t know this either!);
- indexes can have WHERE clauses;
- don’t use the database if it isn’t a good fit!
Nice article! A must read for all the postgresql & postgis users out there!
Google Earth: Updated hi-res images
Good news! Google has updated google earth (and probably google maps) with high resolution images from Belgium. My hometown (Ghent) used to be in google earth a dull low res picture, but now, I even can see my building!
Check it out!
Tips and Tricks for writing PostGIS Spatial Queries (bostongis.com)
The guys from bostongis.com gave a presentation at OSCON 2009 last week about writing PostGIS spatial queries. You can download the presentation here, but these are main points (please note that these tips & tricks are all meant for PostGIS 1.4):
- Aggregates of geometries is way faster than it used to be;
- tip: add indexes after bulk insert;
- tip: always run vacuum analyze after bulk insert;
- tip: keep data in form most suitable for your workload;
- tip: use the graphical explain in pgAdmin;
- tip: different scenarios for nearest neighbor queries;
- tip: left joint!!!!!
- tip: simplify geometry to gain performance;
- tip: use CTEs to organize queries;
- tip: use populate_geometry_columns in PostGIS 1.4;
- tip: use the knife trick to bisect geometries;
Some neat tricks… Never the less, I still need to check several new functions: CTE, and windowing. Both are new Postgresql features, are apparently nice optimizations for queries…
I’ll keep you updated!
Geostack Workshop
Couple of days ago, Paul Ramsey and Steven Citron-Pousty gave a workshop at GeoWeb (@ Vancouver, BC) on installing, configuring and using a complete open source geospatial application stack (database, application server, web app & desktop.
Except for the desktop (qgis), all the applications they’ve used are part of the OpenGeo Suite. These are the applications:
database: PostGIS
application server: GeoServer & GeoWebCache
web app: OpenLayers
Desktop: uDig & QGIS
You can download the workshop here. A must see for everybody who wants to learn more about open source geospatial solutions.
Through: OpenGeo – New Geostack Workshop
ArcGIS Server Manager Service doesn’t start
When you find yourself with a ArcGIS Server Manager Service that doesn’t want to start, and you find nothing in the logfiles (mine said something like this: The description for Event ID ( 4098 ) in Source ( ArcGIS Server Manager Service ) cannot be found. The local computer may not hav…), you should check this forum post: http://forums.esri.com/Thread.asp?c=158&f=1697&t=286056&mc=6
It helped me this afternoon…
