PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
1k views

PostgreSQL, find points near points

I'm going to have a database with a 'people' table and a 'shops' table. I'd like to find all people within 500m of a shop, and details from both tables. I'm used to working with mySQL, but I'm ...
2
votes
1answer
2k views

How to restore a back up file into pgadmin III

My backup file containing about 50-60 tables , but I am able to just load only 6 tables for a new db and 31 tables for postgis dbase . And this was the error when I restore a backup file. pg_restore: ...
2
votes
1answer
8k views

How to uninstall PostgreSQL Server on localhost and reinstall with new user and password?

On Windows 7, I'd like to install a brand new, fresh installation of PostgreSQL/PostGIS on a laptop, and wipe ALL the localhost servers, users, data and passwords clean, and then reinstall as if it ...
0
votes
1answer
240 views

Geodjango InspectDB Fails on Postgis Raster_Columns View

I am trying to generate models for an existing Postgresql database using the Django InspectDB command. Models are produced but a failure occurs on the raster_columns view. The error is: Exception: ...
10
votes
2answers
2k views

What are benefits of using ArcGIS with PostgreSQL over simply ArcSDE?

I understand that in order to benefit from ArcGIS spatial datatypes (ST_GEOMETRY, versioned geodatabases etc.) ArcSDE needs to be installed - and with the 10.1 client it is automatically installed (...
1
vote
1answer
201 views

Geometry has incorrect latitude-longitudes

SELECT ST_SRID(geom) AS s FROM zones GROUP BY s; returns a single record: 4326 SELECT Find_SRID('public', 'zones', 'geom'); returns 4326 SELECT AsText(geom) FROM zones returns geometry like: ...
0
votes
1answer
2k views

PostGIS error reads “Geometry has Z dimension but column does not” [duplicate]

I try to store a polygon in GeoJSON format into my PostGIS table. Both are using the CRS EPSG:3857. This is how the polygon is defined. Note that I put in some whitespace to make it more readable. { ...
3
votes
0answers
948 views

Clustering table on geohash index and geohash ordered table

I wonder if there could be a performance gain to re organize the rows in a table by Geohash and then CLUSTER this table on the Geohash index of the geometries. CREATE TEMPORARY TABLE tmptbl AS SELECT ...
1
vote
1answer
546 views

PgAdmin cannot load postgis for a database

Postgis works fine on my other postgresql databases, but I can't load postgis and postgis_topology extensions on a new database. I get following error message: ERROR: could not load library (path to ...
1
vote
2answers
1k views

Geoserver SQL view parameterizing

I've got a postgresql table that has the following attributes: name String begintime String endtime String the_geom Point Now I'd like to create an sql view in geoserver (2.2 ...
0
votes
0answers
22 views

How to find all possible paths by Postgis / Pgrout [duplicate]

There are functions available to find the shortest paths in Postgis / Pgrouting extension. But is it possible to find all the possible paths between two points/nodes ? Is there any such function ...
1
vote
1answer
508 views

Are database client files platform specific?

I'm just looking for some clarification on a question that came up with me this morning. Are the DLL client files (libeay32.dll, libiconv-2.dll, libintl-8.dll, libpq.dll, and ssleay32.dll) platform ...
1
vote
1answer
670 views

PostgreSQL: transform spatial column from PG_GEOMETRY to ST_GEOMETRY

I have spatial Data stored in my PostgreSQL database as PostGIS Geometry (PG_GEOMETRY). It looks for example like this: 010100002031BF0D0000000000F989264152B81E057E6D5841 Now I would like to move ...
3
votes
1answer
220 views

Convert OpenStreetMap schemas to ArcSDE in PostgreSQL database

I am trying to find a way to store OSM Extracts (or possibly the planetfile) in a PostgreSQL database, keep it up to date with diff files and then process the data further with ArcGIS (e.g. Geocoding, ...
2
votes
0answers
32 views

error in loading raster tiles on postgreSQL [duplicate]

I am trying to import raster data from PostGIS to GeoServer. I follow the instructions given on this link. http://docs.geoserver.org/stable/en/user/tutorials/imagemosaic-jdbc/imagemosaic-...
0
votes
2answers
3k views

Cannot insert simple geometry data

I installed PostgreSQL 9.2.4 and PostGIS 2.0.3 on my Windows 8 machine. Then I created a table using phpPgAdmin and added a geometry column using the following statement. SELECT AddGeometryColumn('...
0
votes
2answers
656 views

How to select polygons in polygon and export to CSV?

I have two polygon layrers, each layer a table in PostgreSQL: One layer with polygons areas in a city. Another layer with buildings (polygons) that are within areas. How can I to do to know what ...
0
votes
1answer
834 views

Cant Instal psycopg2 on mac 10.8.2

I know there Have been a lot of similar questions asked but none address this issue as I far as I can tell. When I am trying to install the sextante plugin, or run PostGIS Manager in QGIS I receive ...
1
vote
1answer
770 views

PostGIS not converting GIS data into latitude/longitude values

I am running PostgreSQL and PostGIS. I am trying to do something mind-numbingly simple: determine the latitude and the longitude of a point. Unfortunately, PostGIS stubbornly refuses to transform the ...
2
votes
0answers
142 views

Installing knn on PostgreSQL for windows

I am trying to install the extension KNN for clustering as an extension on my PostgreSQL database. I have downloaded the file http://www.pgxn.org/tag/analysis I found instructions on how to ...
0
votes
1answer
595 views

ST_MakeEnvelope returning too many geos

I am using the following query in my db: SELECT bg_id, ST_AsGeoJSON(the_geom) FROM bg WHERE bg.the_geom && ST_MakeEnvelope(xxx, xxx, xxx, xxx); It seems to execute alright but when I draw ...
9
votes
2answers
5k views

Implementing Spatial Autocorrelation using QGIS or PostgreSQL or any other free application?

I am a total newbie on the GIS scene so I hope this is not a stupid question. I have two layers in a PostgreSQL database. The first layer contains points where events happened in Europe. The second ...
2
votes
1answer
71 views

Preserving attributes of linejoin through concatenation

I've run into a bit of a problem, I'm working on a layer of connected lines which represent geological faults and contacts. These lines were broken into many small segments so to get rid unnecessary ...
0
votes
0answers
116 views

Shooting star algorithm in postgres 8.4 and postgis1.4 showing fatal error and remaining are working fine

When I try to run this shooting star algorithm, the postgis suddenly loosing connection to postgres and showing error no connection to server SELECT * FROM shortest_path_shooting_star(' SELECT gid ...
1
vote
1answer
174 views

Win7 Senior users_Which pgRouting and postGIS versions are stable with postgreSQL8.4?

I'm working on a postgreSQL ,postGIS and pgRouting project on Windows 7 I need to work on a stable version (no time for risks), and I can see that many people are asking the same question many places....
1
vote
5answers
3k views

Unable to install the PostGIS extension to PostgreSQL on Ubuntu 12.04 LTS

I just installed Linux Ubuntu 12.04 LTS (on a virtual machine). I then successfully installed the PostgreSQL 8.4 database management system. I am now trying to install PostGIS, which is an extension ...
2
votes
1answer
1k views

How to install multiple versions of postgres in same os windows 7 64-bit?

I have already installed PostGIS along with opengeosuite and it is the latest version. Now I want to run pgrouting but the latest stable version does not compatible to the latest postgres versions. ...
1
vote
2answers
799 views

Public PostGIS Server available?

Can anyone point me to a (if possible at all) list of public PostGIS Servers, where one can set up their own database/tables? I would need a PostGIS server on 24h/7d while not having the resources by ...
0
votes
1answer
501 views

How to install PostGIS 8.4 in windows 7 64 bit parallel to an existing PostGIS 9.2?

PostGIS 9.2 is installed along with OpenGeoSuite using one click setup. But 9.2 dont support pgRouting. So I need to install 8.4. I dont how to include binaries and all. So someone please tell me how ...
2
votes
0answers
542 views

QGIS SPIT errors loading shapefile into PostGIS

With QGIS 1.8 on Windows 7, I just installed PostgreSQL / PostGIS and tried to load my first data into it, a shapefile using SPIT, but failed: ERROR: Geometry type (Polygon) does not match column ...
1
vote
1answer
883 views

raster2pgsql missing in Opengeo-suite installation

I installed opengeo-suite 3.0.2 on Ubuntu 12.04 and although the stack seems to be working fine, raster2pgsql is no where to be seen! root@mywebsite:/usr/local/lib/postgresql/9.2# locate raster2pgsql ...
4
votes
1answer
4k views

How to import QGIS Raster into PostGIS

I have created a IDW heatmap using QGIS 1.8.0 and need to import the raster into PostGIS 2.0.1. QGIS saves the raster layer as a .asc file. How do you import the raster into PostGIS as a Raster? What ...
17
votes
2answers
5k views

Which function for creating a POINT in PostGIS?

When defining a Point in PostGIS, when do you decide to use which of the following? ST_SetSRID(ST_MakePoint(lon,lat),4326) ST_SetSRID(ST_Point(long,lat),4326) ST_SetSRID(ST_GeomFromText('POINT(lon ...
1
vote
1answer
252 views

Postgis invalid geometry from shape file

I'm having trouble converting a Feature class (shape file) to a DB table (Postgresql 8.4/Postgis 1.5). This is the command in the pg log: INSERT INTO "public"."distritos" ("Shape" , "DEPARTAMEN" , "...
2
votes
1answer
1k views

Get the lines between all points of a polygon in PostGis : avoid nested loop?

I am trying to get all the lines between the points of a polygon, by writing PostGreSQL functions. I have written the two following functions that do the job fine : the first function to get all the ...
2
votes
0answers
178 views

Creating polygons for a choropleth map using PostGIS

I have a grid of points, each with a value to it as shown in the image below. The data was retrieved from PostGIS and rendered in Quantum GIS. The final result should be a polygons of a choropleth map ...
6
votes
3answers
392 views

What is the easiest one click install method for PostGIS and it's entire dependencies?

For someone new to database servers and their dependencies, what is the easiest assured way of installing PostGIS on a machine with no other software beyond what comes with its operating system? I'd ...
3
votes
3answers
4k views

Connnect to PostGIS db using QGIS - when not on localhost

I"m just getting started with PostgreSQL/PostGIS db (v9.2) and I'm wanting to connect to db while not on the localhost using QGIS. I've been able to connect successfully on localhost, however I'm not ...
0
votes
2answers
125 views

Is PostGIS doing a full table scan?

I am trying to retrieve all points p that are within polygons c. While building the query, I came across this strange behavior. Question: Why does query 2 takes so long compared to query 1? Is it ...
1
vote
0answers
147 views

I can't connect QGIS with postgresql

I can create a connection to the database in qgis and the test connection is successful. However when I actually connect to the database to export shape files I get the following error: the ...
3
votes
2answers
2k views

Problem loading PostGIS (Postgres) View layer in QGIS

Why is Postgres user XX via command „Add PostGIS Layers“ in QGIS enabled upload only View tables which owns? Or how to achieve it to the user XX via PostGIS can load View table which owns the user YY? ...
0
votes
1answer
262 views

Creating the node ids:Is this SQL error related to geometry type?

I loaded a multilinestring layer into PostGIS Manager: and use the SQL query, trying to get the table with the node ids: ALTER TABLE split_4 ADD COLUMN source integer; ALTER TABLE split_4 ADD ...
6
votes
1answer
643 views

How can I make a PostGIS ST_Distance_sphere query more efficient?

Within PostgreSQL/PostGIS I have written a SQL query to get the closest distance for each element within a point coordinate table from a polyline table. The query comes back and works correctly, but ...
2
votes
1answer
170 views

assign_vertex_id:problems when creating the node ids

Before using pgrouting, I have to create the node ids. I loaded my polyline layer into PostGIS Manager: I type this in SQL query: select assign_vertex_id('split_4', 800, 'geom', 'gid') But as ...
5
votes
2answers
2k views

Which spatial DBMS should I select?

I found there are a number of RDMS have the spatial capability e.g. mysql, postgreSQL I considering which I should go forward! I would like to store the lat lon coord from the tracker to the db ...
2
votes
1answer
347 views

SQL Command to convert pgRouting result to GeoJSON

My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to ...
2
votes
1answer
717 views

Force dimensionality with shp2pgsql

After invoking: shp2pgsql -W "latin1" theshape.shp > thesql.sql My console responds with: Shapefile type: PolygonZ Postgis type: MULTIPOLYGON[2] Note the shapefile type is PolygonZ. The result ...
0
votes
1answer
118 views

Schema for offline map editing and pass to production environment

I'm working with QuantumGIs and PostgreSQL/Postgis. So when I edit the layers, the changes are reflected on the DB. The thing is I want to do some "offline" editing before committing the changes to ...
3
votes
1answer
277 views

Get Multiple Shortest Path using PgRouting in One Query

I am trying to get multiple shortest path using A* Algorithm available in PgRouting. I referred Solution given by UnderDark ( Link of the Stakoverflow Post ) . But it is not giving me proper Output. ...
0
votes
1answer
130 views

In QGIS can I show just the lines in a Postgis query? How can I do that?

I have three tables in Postgresql, each table with one layer of lines. I want to show the path of an initial line to another final. Is it possible executing a SQL query? Thanks My three tables are ...