The tag has no usage guidance.

learn more… | top users | synonyms

9
votes
1answer
589 views

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 ...
6
votes
1answer
177 views

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 ...
5
votes
1answer
983 views

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 || ')' ), ...
5
votes
2answers
944 views

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) ...
5
votes
3answers
2k views

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 ...
5
votes
1answer
361 views

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 ...
4
votes
2answers
1k views

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 ...
4
votes
1answer
8k views

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 ...
4
votes
1answer
96 views

Using SELECT to call a function in a jdbc migration

I'm looking to set up some Postgres/PostGIS migrations with clojure/jdbc. running side-effect functions with SELECT is proving to be an issue, with most migration libs eventually throwing the A ...
4
votes
0answers
290 views

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 ...
4
votes
0answers
307 views

User locking tables without privileges [closed]

This is a restatement of a question asked on the GIS Stack Exchange here. Although it has GIS-specific elements it may be be a more general PostgreSQL effect. I have a PostgreSQL 8.4.4 database ...
3
votes
2answers
3k views

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 ...
3
votes
1answer
7k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
1answer
248 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
3
votes
1answer
140 views

RDS raising unwanted PostGIS NOTICEs

I have an Amazon RDS instance that is producing unwanted NOTICEs. I have never seen this behaviour before. mydb=> select current_setting('client_min_messages'); current_setting ...
3
votes
1answer
4k views

Postgresql subquery speed much slower than individual queries

In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal ...
3
votes
1answer
1k views

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 ...
3
votes
1answer
635 views

PostGIS ERROR: operator does not exist: postgis.geometry @ geometry

I have had PostgreSQL 9.3.1 and PostGIS 2.1.0 installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby ...
3
votes
1answer
4k views

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 ...
3
votes
0answers
49 views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table ...
3
votes
0answers
210 views

2 versions of Postgis installed

For some reason I manage to have 2 versions of postgis installed at the same time due to a wrong update. I can see this as by issuing a select postgis_full_version() i get back version 2.1.0 ...
2
votes
2answers
419 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
2
votes
2answers
537 views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
2
votes
2answers
1k views

QGIS PostGIS Authentication Failure with “trust” option

I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, ...
2
votes
1answer
59 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE ...
2
votes
1answer
64 views

Using an index on a Postgresql integer range causing trouble

Unfortunately I was not able to create a usable example / fiddle. But I am using a Postgresql 9.4 table that looks somewhat like this: CREATE TABLE table1 (id INT PRIMARY KEY NOT NULL, dtype TEXT, ...
2
votes
1answer
359 views

Problem with shortest distance calculation in PostGIS

While working with PostGIS pgrouting, for calculating the distance between two roads (lines) I'm using the shortest_path function. The logic is based on start_point(Start_id) and end_point(end_id) ...
2
votes
0answers
51 views

Access points of geometric path data in PostgreSQL 8.1

Summary: I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to ...
2
votes
2answers
49 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...
2
votes
1answer
283 views

What does: ERROR: tuple offset out of range: 0 mean?

It seems (but I can't tell for sure) that after I added a postgis indexed column (USING gist) to my database last week, I have been seeing a lot of those errors: ERROR: tuple offset out of range: 0 ...
2
votes
1answer
215 views

Scaling of PostGIS vs MySQL cos/sin calculations [closed]

I need to sort database rows according to GPS coordinates (or any other geographical representation). The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least ...
1
vote
2answers
304 views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
1
vote
1answer
51 views

Why does this PostGIS query take so long to run?

I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing ...
1
vote
1answer
225 views

Installing PostgreSQL and PostGis on Mac OS X

I downloaded and installed PostgreSQL and PostGis from http://www.kyngchaos.com/software/postgres, I work on OS X 10.10. When I go into the terminal now and type psql it says -bash: psql: command ...
1
vote
1answer
54 views

PostgreSQL : send all rows of a table to a function

I have a table with one column (double precision numbers). I also have made a function (with double precision numbers as input and output), and I want to send every value of my table to the function, ...
1
vote
1answer
1k views

Why is Postgres on 64-bit CentOS 6 significantly slower than Postgres on 32-bit CentOS 6

We have some Postgres + PostGIS applications that run well on CentOS 6 32-bit machines. We've recently been testing them on CentOS 6 64-bit machines, with similar configuration (all our machines are ...
1
vote
1answer
44 views

Difficult migration from Postgres 8.1 (postgis 1.5) to Postgres 9.4 (postgis 2.1)

I am in charge of a system developed 8 years ago. When I make a dump of the postgres 8.1 database, all the postgis 1.5 functions are included. I have successfully migrated it to a postgres 9.1, ...
1
vote
1answer
56 views

Unexpected results with soundex() function when combined st_distance()

select soundex(A.name), A.gid, A.name, soundex(B.name), B.gid, B.name, st_distance(B.the_geom,A.the_geom) * 111.325 as dist from (select name, ...
1
vote
1answer
185 views

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 ...
1
vote
1answer
70 views

Are PostgreSQL dynamic arrays more wasteful than plain text?

Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to ...
1
vote
1answer
143 views

PostGis getting rid of the lat long from table

This question is related to this question : Order by distance Now my tables have changed a bit. Table cafes now doesn't have latitude, longitude columns instead there are table addresses which ...
1
vote
3answers
148 views

Trying to create PostGIS database

I am trying to create a spatially enabled PostGIS database by following the PostGIS documentation. In the short version, i executed, createdb yourdatabase createlang plpgsql yourdatabase But I did ...
1
vote
1answer
24 views

postGIS schema qualifier - best practice

There are a number of cases where the EXTENSION for postGIS is created iwth a schema qualifier. CREATE SCHEMA postgis; CREATE EXTENSION PostGIS WITH SCHEMA postgis; why is that? what are the ...
1
vote
0answers
39 views

The definitive fastest radius search query with Postgis 2.0

I come from mysql world. I read the manual and saw some examples, after that, I'm not able to say witch method is the fastest for a radius search. Nobody seems to agrees. My simple case: I need to ...
1
vote
1answer
52 views

How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial ...
1
vote
0answers
19 views

How can I connect MapGuide Maestro to PostgreSQL/PostGIS?

I am trying to connect my database PostgreSQL/PostGIS to MapGuide Maestro but it sends me an error message: Do you have some idea about this or can you help me about how I can connect it with ...
1
vote
0answers
22 views

PostgreSQL: Improve system performance, KNN modification with GiST Index

In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on ...
1
vote
0answers
27 views

PostGIS radius search performance

In my Postgres database, there is a listing table, inside that table, I have a column called geo_point, which stores latitude and longitude information. The column type is geometry(Point,4326). I ...
1
vote
0answers
11 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
1
vote
0answers
133 views

PostgreSQL suddenly using all my disk space

Today I fired up my linux mint via my virtual computer (host is windows 8), left for 30 minutes, and when I was back, I got an error saying I was out of disk space. Yesterday I had about 20 free GB ...