Posts Tagged ‘PostGIS’

Posted: June 11, 2015 in GIS
Tags: , , , ,
This is strange, but I was unable to find instruction about importing QGIS layers into PostGIS database with PyQGIS API. The PyQGIS cookbook has the example of exporting layers as .shp-files via QgsVectorFileWriter.writeAsVectorFormat() function and says that the other other OGR-supported formats are available to use in this function as well. PostGIS is supported by OGR so people got confused and try to use this function to import data to the PostGIS with no success and have to write generic import functions. 

After of couple of hours of searching the internet for the solution I gave up and decided to find the answer the hard way: started to search through the source code for the DB Manager plugin that has this nice “import layer” feature. It took about 20 minuets to trace down the function that was in charge of the import and it was QgsVectorLayerImport.importLayer(). But the quest wasn’t over yet! The documentation says nothing about provider names that are accepted by this function. “PostgreSQL” would be the obvious name for the provider as it is the name for the PostgeSQL provider in OGR, but it is not the case. I had to go through the source code of DB Manager again and luckily in comments (and there are quite a few of them in DB Manager: I didn’t find a single doc-string there) the author wrote that it is “postgres” for the PostgreSQL.

Now here is the very basic example code of importing QGIS layer into PostGIS:

uri = "dbname='test' host=localhost port=5432 user='user' password='password' key=gid type=POINT table=\"public\".\"test\" (geom) sql="
crs = None
# layer - QGIS vector layer
error = QgsVectorLayerImport.importLayer(layer, uri, "postgres", crs, False, False)
if error[0] != 0:
    iface.messageBar().pushMessage(u'Error', error[1], QgsMessageBar.CRITICAL, 5)

There is one question about spatial technology that bothering me for a couple of years.

Is anyone suitable for working with GIS and spatial data or one should be a professional?

Well that’s a tough question. But in general it depends on conditions.

Lets ask ourselves two more other questions.

  1. Is anyone suitable to operate PC, printer or database?
  2. Is anyone suitable to be system administrator in your office?

Obviously it is “yes” for the first question and “no” for the second. Anyone can ride a bicycle but one need license to drive a bike.

So my answer for the given question is – one need to learn a lot to understand how to work with spatial data. Anyone willing to learn theory is suitable to work with spatial data.

Tonight one smart ass (or should I write “cat”?) asked me why in the hell I suggest to calculate distances (when using PostGIS) in the corresponding zone of Pulkovo-42 for Russia (EPSG:28403 for example) when possible? The motivation of the question was based his statement that one will need far shorter SQL-query for distance measurement if the geometry would by casted to geography type and distance would be calculated with EPSG:4326. Like: “Eah, ST_Distance will return meters even for ellipsoidal calculations! Did you know it, dumb ass?! Admit, you suck!”

Well, kind reader, can YOU see the GLOBAL difference between  EPSG:28403 and EPSG:4326 (it is not about projection)? The answer is “reference ellipsoid” (or “geodetic datum” or just “datum”). This reference ellipsoid defines the Earth shape model parameters for all the calculations that one would perform with spatial data.

Well, kind reader, if you were (are) a good student then you should understand, that if reference ellipsoid defines the MODEL of the shape, then it also defines the BIAS of this model from the true value at any given location.

The next questions you may ask:

Q: Does this bias matters?

A: Well, it depends on your goal requirements and the answer will be “NO” or “YES”. So you may ask me here if in doubt.

Q: How big is the difference between measurements using different datums?

A: Well, it depends 😉 … depends on datums compared as well as locations compared – from centimeters to hundreds of meters.

Q: Will 1 meter difference make your day?

A: Absolutely! Do you have a land parcel in your possession? Imagine that tomorrow some people from the municipality will came to you and demand to move you fence, because it is build on the adjusted road land parcel. They show you a digital map where your fence is 1 meter on the adjusted parcel. And this shit happened because of 1 meter bias!


Instead of conclusion

If you’re a cat – grab a handbook on geodesy and become a human being, or… just keep licking your balls and quit work with spatial data before you’ll ruin something.

I needed to create design for a spatial database and found out that despite of availability of the tools for a regular database design, there is a lack of such tools for a spatial database design.

Fortunately there is a suitable free cross-platform tool – MOSKitt Geo, which allows to design spatial databases for PostGIS and Oracle. MOSKitt is not quite user friendly and if you are not an experienced software designer, you will need to read the f*** manual.

  • Download MOSKitt itself and extract it (installation is not required). Note that x86_64 version (and version for MAC) is available for 1.3.2 and earlier releases (current is 1.3.8).
  • Install MOSKitt Geo plugin using instructions from here. Note that for 3-rd step in instruction you may also need to type something and erase it in the search area of the dialogue to make list of plug-ins appear (at least I needed to). And one more thing – MOSKitt Geo repository is already there, so you don’t need to add it – just choose it from the drop-down menu.
PostgreSQL/PostGIS database connection and reverse engineering

To connect to existing database go Window->Show View->Other->Data Management->Data Source Explorer

 A new tab will occur:

Right click on Database Connections and choose New->PostgreSQL. You will see a dialogue for the DB connection settings. Note that there might be no PostgreSQL JBC Driver defined. In this case first download needed driver and then click on the icon to the right from the driver drop-down menu (New Driver Definition).

Connection settings


In the Edit Driver Definition dialogue provide path to the driver and click Ok.

Driver definition dialogue

When database connection established, right-click on it and choose Reverse Engenieering.