The tag has no wiki summary.

learn more… | top users | synonyms

6
votes
0answers
241 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 ...
4
votes
2answers
788 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
161 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 || ')' ), ...
4
votes
1answer
3k 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
2answers
379 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) ...
4
votes
1answer
268 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
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 ...
4
votes
0answers
162 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
1answer
116 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
910 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
251 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 ...
2
votes
2answers
208 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
1answer
4k 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 ...
2
votes
2answers
114 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
544 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
1k 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 ...
2
votes
1answer
289 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
33 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 ...
2
votes
2answers
17 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
0answers
90 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
1answer
137 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
167 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
125 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
2answers
94 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 ...
1
vote
1answer
43 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
38 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
95 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
1answer
816 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
2answers
114 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
165 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 ...
1
vote
0answers
52 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
1answer
40 views

Howto do a hard upgrade to postgis-1.5 to postgis-2.0

I need to upgrade from postgis 1.5 to postgis 2.0 but i cant backup my data and restore it can someone give me a link where it is clear enough for doing this?
1
vote
1answer
1k views

PostGIS “could not open extension control file” when creating extension

I've followed the instructions here to install PostGIS http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt but when I went to create the extension I got the following error. I'm ...
1
vote
0answers
125 views

PostgreSQL how to create a scalable location-based query

I need suggestions because I am not good enough. I have a DB in PostgreSQL that runs on AWS (Amazon Web Services). I have a table "user_location" where there are stored the locations of each user ...
0
votes
1answer
143 views

Best read performance when every query is known, and the data doesn't change

I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or ...
0
votes
1answer
43 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
0
votes
2answers
1k views

Difficulty with Postgis extension on Ubuntu 14.04

I have a updated Ubuntu 14.04 running postgresql 9.3 (installed using the tutorial from https://wiki.postgresql.org/wiki/Apt). When I access my database through pgadmin and go to the console and ...
0
votes
1answer
57 views

Trying to map postal codes to their respective cities, but running into some issues

The Problem I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now: Now, let's take Tuskegee University ...
0
votes
1answer
75 views

Why my query works on VIEW and doesn't work on a similar table?

I have a view openstreetmapview that combines two tables with different columns. The first table is openstreetmap and the second is B. When I execute this query on the view: select this_.gid ...
0
votes
1answer
202 views

Deciding the best way to partition (Postgresql)

I have been trying to research this for quite a while now and I am having a hard time trying to figure it out. I am out of ideas other then setting up an extra test database and running through all of ...
0
votes
2answers
273 views

Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12

At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2? I checked their website and to my understanding it is not possible... I hope that's not the case. Can anyone ...
0
votes
0answers
40 views

MongoDB to store vehicle tracking information

I am building an app similar to Uber for tracking vehicles. Since the update frequency is so high (accounting for several users), I want to know the general practices involved for making writes faster ...
0
votes
0answers
23 views

How to store multiple raster files in postgres / postgis efficiently

This question is partly linked with another question on GIS:stackexchange, but I believe it is has more to do with clever data management. I have a Postgresql server with Postgis extension running ...
0
votes
0answers
46 views

How to deal with finding nearby locations when given a zip code or city?

I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and ...
0
votes
1answer
70 views

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases: /usr/lib/postgresql/9.3/bin/pg_dump --schema-only -h stg-db.....rds.amazonaws.com ...
0
votes
0answers
93 views

GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys

I'm trying to execute the following statement in a Rails app that uses postgis. Postgres 9.3.4, Postgis 2.1.3 (with homebrew on Mac). ActiveRecord::StatementInvalid: PG::InternalError: ERROR: ...
0
votes
0answers
88 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
0
votes
0answers
182 views

PostGres- How to loop in Table to update every record in Table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update the ...
0
votes
1answer
146 views

Problems using “Excel PostgreSQL Import, Export & Convert Software”

I want to export data from Postgresql, and I tried many ways then find the tutorial of Excel PostgreSQL Import, Export & Convert Software on Youtube. I installed the software, but I could not ...
0
votes
0answers
162 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...