The Stack Overflow podcast is back! Listen to an interview with our new CEO.
42

I don't know what version of PostGIS you are using but on >2.0 I first login using psql: psql -U postgres Then I create a database: CREATE DATABASE example_gis; Then I move into this database: \connect example_gis; And then I run the commend: CREATE EXTENSION postgis; This creates all the spatial functions, and object types in this database.  


21

Don't check "Save passwords" (and/or "Save username") in the connection - and QGIS will ask for credentials when it needs them.


19

This appears to be a known issue that won't be fixed: see http://hub.qgis.org/issues/5328 If you need a workaround, try changing your table's geometry column type to a generic 'geometry': ALTER TABLE my_table ALTER COLUMN geom SET DATA TYPE geometry; After you've done your import, you can revert back to MultiPolygon: ALTER TABLE my_table ALTER COLUMN ...


18

I use ogr2ogr to automate ingest of shapefiles into a PostGIS database. Specifically with regard to the question, use the option: -nlt PROMOTE_TO_MULTI This will force ogr2ogr to promote POLYGON geometries to MULTIPOLYGON, avoiding the error. A very simple example: ogr2ogr -f "PostgreSQL" PG:"dbname='<my_db>'" -nlt PROMOTE_TO_MULTI <shapefile> ...


18

I made a test for you: PostgreSQL 9.3 PostGIS 2.1 Windows 7 i7 3770@3.4 GHz processor GDAL 2.0-dev 64-bit shapefile of 1.14 million polygons, file size 748 MB Ogr2ogr command: ogr2ogr -f PostgreSQL PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" test.shp --config PG_USE_COPY YES -nlt MULTIPOLYGON Total time:1 minute 30 ...


16

try opening the dbase-file with libreoffice (or openoffice) and try different encodings, if utf-8 doesnot work try iso-8859-1 and look if the chars öäüß are displayed correctly


15

Aside from Spatialite, you might also want to consider PostGIS. Think of it as Spatialite's big brother ;-) It's just another data source for QGIS while you can connect to it using the RODBC package in R.


14

Spit is unmaintained and not recommended anymore. I'd suggest using the processing toolbox and choosing the "Import into PostGIS" algorithm. I've had much more luck using that routine. A few things to note: The database (connection name) parameter must match what you've named your database connection from the "Add PostGIS layer" dialog. The schema must ...


13

The real advantage to spatial databases (PostGIS, spatial extensions to MySQL or anything else) is that you can do spatial operations on spatial data. If you are just storing point coordinates, then you don't really gain much from spatial (just use two numerical columns). If you store combinations of point coordinates (where the customers are), and line ...


13

For displaying purposes it is always good to use a spatial index. It will improve speed of both rendering and spatial queries. However, if you plan to update large quantities of objects, it might be wise to remove the spatial index during the update. Otherwise the update process will become significantly slower, because with every update the spatial index ...


13

Why use a database? Because it's not necessarily the case, especially with larger datasets, that you can expect to be able to push the entire thing to the client. If you're talking thousands of points, then sure, but for millions of points you probably don't want each and every one represented in RAM on your end users' client. Not everyone has a super fast ...


13

Short and sweet: Install changeDataSource plugin. Right-click on the layer in the Layers Panel and select Change vector datasource. In the dialog box that appears, just hit OK without changing any of the settings. Basically, you are "changing" the vector datasource to what it already was, but this forces QGIS to reload the layer definition. Tested in QGIS ...


12

I suggest you to add Spatialite to your list. It has the benefits of being file based (as a shapefile, or an .mdb database) and supports most of the usual spatial operators and tpyes, that you can find in PostGIS too. Spatialite is based on Sqlite, so you will manage a single file, that you can easily move and share.


12

QGIS supports PostGIS - including a dedicated PostGIS Manager plugin for seamless work Spatialite - there is also Spatialite Manager MSSQL 2008 Spatial - also supported by default starting from version 1.8 Oracle Spatial - via OGR but not by default & Oracle Spatial GeoRaster plugin ESRI File/(Personal) Geodatabase - via OGR All database connections ...


12

You can achieve this by preparing all those layers in one (or more) project files. Share these projects on the network and you can load them using Layer - Embed Layers and Groups. If you want to change anything in these layers, open the original project file and changes will appear in all projects that contain embedded layers.


12

It is definitely possible to pull all the layers into one database, although in a multiuser environment, this will be best if most users will only be reading (not editing) the data. The easiest way to quickly convert a large number of shapefiles to a single SpatiaLite database is to use ogr2ogr. Make sure your shapefiles are in a single folder (e.g. ...


12

select st_asgeojson(st_transform(ST_SetSRID(geom, XXXX), 4326)) AS geojson FROM ptable where level_1='Built Up' Try setting SRID beforehand by replacing XXXX with the code of your SRID.


11

Contraction Hierarchy is a very fast algorithm: http://algo2.iti.kit.edu/1087.php This algorithm is RAM friendly while executing a query (to hold a contracted graph some more RAM is necessary as well as massive preprocessing) There are some other algorithms - including the ones that solve public transit routing: http://i11www.iti.uni-karlsruhe.de/members/...


11

If all you need is the tables of IDs, text, numbers (no geometries), then your best option is to use ODBC. You can install a MySQL ODBC driver for your system: http://www.mysql.com/downloads/connector/odbc/ download "Windows (x86, 32-bit), MSI Installer". (ArcGIS is still a 32-bit program, even on a 64-bit computer, so you will always require a 32-bit driver ...


10

Unfortunately, some of those feature are deeply dependent of the piece of software you use. For example, let's take the U-Turns in ArcGIS and PGRouting. In ArcGIS, it is something you can choose as an option, in PGRouting, it is nested in the code. On way street can be processed in two way. The are set up part of the graph with a very high cost (PGRouting)...


10

In Addtion to saving defined layer styles within project files on the network, you can create a single Spatialite enabled database.sqlite, if your workflow isn't too busy. For myself, I've replicated a fgdb by importing a hundred shapefiles and a couple hundred datatables, with great performance. Spatialite won't allow for PostGIS or FileGDB style mutli-...


9

Not sure if you will be able to work with MS Access in QGIS (plus I haven't heard about any respectable GIS-project that would use MS databases); MSSQL - is supported (but never tried it myself and never will ;-) ). Consider possibility to use Spatialite (spatial extension of SQLite). It will be quite suitable to operate Spatialite DB in QGIS and you can ...


9

Here is one way to do that: Components used: Leaflet PouchDB (a framework that uses indexedDB in your browser) XHR2 (XMLHTTP) to fetch the files, since JQUERY does not yet support binary blob XMLHTTP KendoUI (an HTML5 web control library) Running the Demo: http://codepen.io/DrYSG/pen/hpqoD Delete any old DB (Press the Delete Button) Reload the page ...


9

After the suggestions of user30184, Paul Ramsey and my own experiments. I decided to answer this question. I failed to mention in this question that I am importing data to a remote server. (although it is described in the blog post I refer to). Operations such as inserts, over the internet are subject to a network latency. Perhaps it is not irrelevant to ...


8

Assuming the columns appear in time order, the first row (for example) indicates that total construction through each period went 0, 0+45 = 45, 45+135 = 180, 180+405 = 585, 585+1010 = 1595, ..., 2230+0 = 2230. Construction was halfway through at 2230/2 = 1115. This occurred during period 4, because at the end of period 3 the total was 585, at the end of ...


8

I did call ESRI support about this and their answer wasn't encouraging, but it did explain the problem. Paraphrasing ESRI: The problem is that ArcGIS Desktop, being 32-bit software, is limited to using 4GB of RAM at the most. The text file has to be processed in RAM before being stored as a table, so at some poing during processing ArcGIS was hitting the RAM ...


8

You should certainly consider the hugely popular, well established, widely supported (and free) PostGIS. It will do every thing that MySQL can do and handle spatial locations as first class objects. Thus you can carry out selections based on points with in a bounding box (or other polygon) without having to write out all the comparisons etc. Once you start ...


8

You can easily coerce your data.frame to an sp SpatialPointsDataFrame object. coordinates(datosmerged) <- ~XCOORD+YCOORD class(datosmerged) Assuming that "shape" is a polygon, you can then use the "over" function to identify overlap between the two feature classes. An alternative is "gIntersects" in the "rgeos" package but it a bit overkill for you ...


7

For spatial database with raster support you can use any of the following, depending on your requirements: PostGIS Raster: From their website - "PostGIS Raster is an ongoing project aiming at developing raster support in PostGIS. It is a new project very different from the previous PGRaster project and also very different from Oracle Spatial GeoRaster. ...


7

PostgreSQL has a column limit of between 250 and 1600 "depending on column types", and supports spatial data and queries with the PostGIS extension. So I would be inclined to do two things: First, where a column represents a category rather than free text, create a separate table with those categories, and replace the column with an integer ID and foreign ...


Only top voted, non community-wiki answers of a minimum length are eligible