Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgis]

PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.

255 questions
19
votes
3 answers

PostGIS vs. SQL Server for GIS data

So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue with this, we are a 95% SQL Server and 5% Oracle…
LowlyDBA - John M
  • 10,812
  • 11
  • 39
  • 59
19
votes
2 answers

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to "/root" and the Password: prompt appeared twice! Is…
Nyxynyx
  • 971
  • 6
  • 13
  • 22
17
votes
3 answers

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping spatial polygons. That being said, our database has…
Ryan Charmley
  • 271
  • 1
  • 2
  • 5
15
votes
3 answers

Order of columns in a compound index in PostgreSQL (and query order)

I have a table with 50K rows. It is actually a PostGIS table. The query has 4 parts (1 mandatory) (3 Optional) intersection box (a geography rectangle) with 4 lat,long (I use st_intersects) [Mandatory] Date Range (min, max) on a date field File…
Dr.YSG
  • 319
  • 1
  • 3
  • 12
12
votes
1 answer

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(-76.000000…
Gandalf StormCrow
  • 535
  • 1
  • 6
  • 16
12
votes
1 answer

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a different variety of weather data (temp, dew point,…
bshender
  • 231
  • 2
  • 4
8
votes
1 answer

PostgreSQL/PostGIS 9.6 broke my compound index

In PostgreSQL 9.2 I had no problem creating the an index that had both a geography (postGIS) type and and integer as a compound index. But now (9.6) it complains on creation of the index and I don't understand the hint it is providing: The columns…
Dr.YSG
  • 319
  • 1
  • 3
  • 12
8
votes
1 answer

moving large PostgreSQL/PostGIS database

I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1). The upgrade process is well-documented. The problem is I don't have enough space on server1 to…
kontextify
  • 431
  • 1
  • 4
  • 9
7
votes
1 answer

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ST_GEOMFROMGeoJSON() function causes to server to…
stUrb
  • 697
  • 2
  • 9
  • 15
6
votes
1 answer

Problems installing Postgis extension on Ubuntu 14.04

I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple: sudo apt-get install postgresql-contrib postgis postgresql-9.3-postgis-2.1 However, this…
gregoltsov
  • 165
  • 1
  • 8
6
votes
1 answer

Limiting number of results in a Partition using OVER(PARTITION BY)

In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the subquery with WHERE row_num < 3? Query SELECT pid,…
Nyxynyx
  • 971
  • 6
  • 13
  • 22
6
votes
1 answer

Troubleshooting Postgresql spatial query failure. Next steps?

We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be sure and also so that the resulting bug report is…
gsiems
  • 2,720
  • 2
  • 17
  • 25
6
votes
3 answers

Install PostGIS v2 for PostgreSQL v9.2

I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension. I've been searching for it for 3 days now, however can't find it. I did however find PostGIS v2 for PostgreSQL v9.1, thanks to this site:…
Paul Meems
  • 163
  • 6
5
votes
1 answer

Error in PostgreSQL/PostGIS Access to offline bands disabled

I am using postgis functions i.e. ST_Clip, ST_PixelHeight. But I am getting this error - ERROR: rt_raster_load_offline_data: Access to offline bands disabled Actually, I have loaded my raster to postgis using raster2pgsql In raster2pgsql I…
Padfoot
  • 175
  • 1
  • 8
5
votes
2 answers

Pros and Cons of virtualisation technology for database-server and datastorage

Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm asking here. It could be a little GIS specific, but…
Martin
  • 225
  • 1
  • 7
1
2 3
…
16 17