Hot answers tagged spatial-database
25
The OSGeo is a repository for the open source GIS community. There are a few applications highlighted here that may suite your needs.
Quantum GIS (QGIS) -
QGIS can handle a variety of vector and raster GIS data sets. The application interface is intuitive, and has a similar look and feel like ESRI ArcMap. The application may also be installed on a ...
23
PostGIS based on PostgreSQL is a popular database for GIS.
I haven't used it much myself, but a pro is that it's open source and that many other GIS uses it so it have an active GIS community.
19
SDE [ArcSDE] can refer to at least two things: the organization of your data in the database (the SDE Schema) or a service listening for connections from clients (the SDE service). Generally they go hand in glove - the SDE service is bound to an SDE schema in a database.
In its "purest" (or perhaps dirtiest) state, SDE handles all of the spatial ...
17
I am a great PostGIS fan and have no experience with MySQL so I mght be biased.
But from what you write I think of two reasons to switch.
first, it will most surely be much easier to implement new features like the season map you mentioned.
second, when you today do your trigonometry calculations I guess you are doing it outside the db. if you do all ...
17
If your dataset is added to and updated often, then INSERT, DELETE and UPDATE statements which cause the index to be rebuilt may slow the database down.
For bulk inserts, such as loading the entire OSM dataset into a database, it may be quicker to drop the indices and create them again afterwards.
If it is more efficient to ignore an index (for example ...
16
If you use that id for something like as a foreign key in relation to another table your whole database will get in big trouble if you have to move a point for some reason. Probably you then will have to keep the id even if it not describes the xy-coordinates any more.
As a unique key is often the best to have something not telling anything about the data, ...
16
You are confusing SQL and WKT (well-known text). WKT is a like a geometry language to describe shapes, but it is not SQL, which is a language to query and manipulate databases. When working with WKT in an SQL query, it must be text, and not mixed-in with the SQL.
Your query works if you properly format the WKT (remove the ",") and set an SRID. For this ...
15
See this paper (draft version):
Haklay, M. (2010), How good is volunteered geographical information? A
comparative study of OpenStreetMap and Ordnance Survey datasets.
Environment and Planning B: Planning and Design 37(4) 682 – 703.
for more rigorous assessment (in UK context).
This one for assesment (in comparison wth Google Maps & Bing ...
15
GeoJSON here are the SPECs.
Here's an example of a line and a polygon:
{ "type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
"properties": {"prop0": "value0"}
},
{ "type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [
...
13
I feel that spatial databases should be treated no differently to traditional databases. They are essentially doing the same thing, storing large amounts of data for fast retrieval. As an example, in PostgreSQL / PostGIS, the geometry is just another datatype. Just like text, or integer. Same in SQL Server 2008. Same in Oracle. If the "spatial" part is ...
13
While your database is technically spatial at this point, you would only be working with the inbuilt pg geometry types.
In order to complete your installation, you do need to run the scripts as suggested.
You can find them in your /usr/share/postgresql/[version number]/contrib/ postgis sub-directory.
You should see the following:
legacy_minimal.sql ...
12
If you don't need third party support and don't forsee the need to query by type keeping them in the same table works just fine. Alternatively you could use an inheritance model as discussed in chapter 3 of PostGIS in Action.
http://www.postgis.us/chapter_03
From an architecture perspective PostGIS doesn't really care if in a query multiple different ...
12
Speed Tests
There are some very speed tests of shapefiles versus database (PostGIS) for MapServer in this presentation (from 2007).
In summary:
For a dataset of 3 million features running requests for 30 features one after another PostGIS was faster than shapefile (although this may have since changed by a fix to reading the shapefile index)
For a ...
12
Based on your requirements, you may need a GIS stack: server, database, presentation, and then the analysis tools. I'd recommend GeoServer (http://www.geoserver.org) for server, PostgreSQL with PostGIS extension for database (http://postgis.refractions.net).
This combination can enable easy distributed authoring/analysis and publishing using WFS, WPS, and ...
11
[Re-posting my answer from StackOverflow. Man, this is going to be annoying if we have to do this for every question from now on.]
Excellent question. I think Yahoo!'s GeoPlanet can be very useful for this: they provide a pretty exhaustive list of places and the hierarchical relationships between them, and you can either download the list of names or use ...
11
I am working at Lakehead University in Thunder Bay Ontario. I have created the same type of facility in my university last year, with the same needs. I have chosen to use QGIS with a PostGIS database. We do have educational licenses of ArcGIS and PCI for teaching purposes, but I am trying to stay away from all of that. If you have any other questions, or ...
11
If you have Ubuntu 12.04, then you should have PostgreSQL 9.1, which makes things awesome for PostGIS 2.0, where you can use use the new EXTENSION framework. To spatially enable a database, use the DDL from a SQL window:
CREATE EXTENSION postgis;
See other details for installing PostGIS 2.0 from source for Ubuntu 12.04 here.
If you are using PostGIS ...
11
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.
11
I think the fastest way to go at this if you want to host it in your computer would be to use the OpenGeo Suite.
It has a web client, a map application server and a spatial database. You can load the shape files into the database by using its shape file to PostGIS loader. You can then view the layers through the GeoExplorer web app that's bundled with ...
10
To import shapefiles into PostGIS, you can use QGIS PostGIS Manager Plugin. It's a GUI for for shp2pgsql command line function. If you want to load KML files, you'd first have to convert those to shapefiles. This is simple: Just open the KML file in QGIS and save it again as shapefile. If you have multiple KML files, you might prefer using OGR Converter ...
10
The countryInfo file from http://geonames.org (http://download.geonames.org/export/dump/countryInfo.txt) has names, country codes, languages and lots of other info. It is a tab separated list so awk or any text editor will let you select just the columns you need.
10
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 ...
10
The most efficient index for the query expressed in your question is the one on gid as it is the only column that appears in a where expression:
CREATE INDEX table_gid ON table (gid);
You can safely drop the gist index as it will only consume space and slow inserts/updates/deletes down.
Long explanation
As I said the most effective index in your case ...
9
SQL Server 2008 comes with geospatial capabilities in-built. Even the free Express Edition supports the full geospatial features, as far as I know.
Further reading:
SQL Server 2008 Spatial Data
MSDN: Working with Spatial Data (Database Engine)
MSDN: Working with Spatial Indexes (Database Engine)
Basic example from Stack Overflow
9
I started an open source GDAL/OGR ArcGIS plugin project this weekend that gives read support to spatialite and any other OGR vector data source like Google Fusion Tables.
I have it working locally reading spatialite and will cleanup and push the rest of changes this coming Friday. I hope you find it useful.
Update 1:
OK, got it working today. The ...
9
One thing to note is that MongoDB's support for spatial datatypes is horrendously bad for any serious spatial lookup, and this applies across the board with NoSQL last time I checked. I dislike GeoCouch quite a bit less, but it also still has a way to go.
GeoJSON is a fantastic format but to take advantage of the limited (POINT-ONLY) spatial indexes in ...
9
Contrary to what dariapra says, my experience in developing Maperitive tells me that the greatest bottleneck is in actual loading of the data before rendering. It all very much depends on how large the overall stored dataset is and how large is the dataset you are trying to render in one go. If you can load it all up into memory, then shapefiles are probably ...
9
Generally you can make a new geospatial table like this:
SELECT *
INTO europe.borders
FROM wo_borders
WHERE admin_lvl2 = 'eu';
-- Define a primary key
ALTER TABLE europe.borders ADD PRIMARY KEY (gid);
-- Spatially enable it
SELECT Populate_Geometry_Columns('europe.borders'::regclass);
However, by doing this you are segregating your database ...
9
Desktop
For many users, GIS means ESRI ArcGIS. While expensive in a commercial setting, they have rather generous educational licensing, including the provision of free copies to educators for distribution to students, one per licensed seat per year. I would advise at least ticking this box; I don't think people who learned GIS in other ways are less ...
9
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
