PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
1answer
273 views

Cast record type to geometry type

How can I cast type record /subquery/ to geometry type? My example: select geom from (select (ST_Simplify..... ) as geom and result is type record, but I need to convert to geometry.
3
votes
1answer
721 views

How can I decrease size of geodatabase “Nominatim”?

I downloaded Nominatim http://wiki.openstreetmap.org/wiki/Nominatim/Installation. The database size is more than 400 GB. The speed of geocoding is very slow. I want to increase it. For example, I ...
0
votes
1answer
671 views

How to install pgrouting 2.x on ubuntu 12.04 LTS

I try to install pgrouting 2.0 on ubuntu 12.04 LTS with postgresql 9.2 I have ppa in the system by sudo add-apt-repository ppa:gwibber-daily/ppa answer: `You are about to add the following PPA to ...
1
vote
0answers
205 views

How to run PostgreSQL stored procedures from QGIS? [closed]

I'm trying to figure out if it's possible to run a stored procedure in a Postgres database from QGIS, more specifically I would like to do this from within a python plugin. In this case I'm trying to ...
1
vote
2answers
646 views

osm2pgsql error when import pbf for Nominatim DB

Good day to all, Am getting the following error when running utils/setup.php in nominatim 2.1: Reading in file: /storage/Library/geodata/africa-latest.osm.pbf Processing: Node(54854k 62.1k/s) ...
2
votes
0answers
179 views

Data loss after compressing geodatabase in ArcSDE

I suffer sometimes from data loss after compressing ArcSDE geodatabase, this geodatabase contains versioned feature classes, so the compressing script reconciles and compress the geodatabase: # ...
0
votes
2answers
371 views

GeoAlchemy pg_function.geojson cannot be decoded with json.loads

I asked this quesiton over on StackOverflow and thought it might make more sense to post it here given it's GIS nature. Given this function: features being a list of SQLAlchemy objects template ...
3
votes
1answer
800 views

Retrieve geographic coordinates from OSM imported data

We have just finished importing OSM data into our PostGIS (Postgresql) database. And we are having difficulty understanding the geometry of various shapes imported. We run a query to retrieve the ...
3
votes
1answer
139 views

Add vertices costs to postgresql routable network

In osm2po it's possible to create custom routers as the sample router TrafficSignalsRouter, but the costs seem to be added on vertices after the routing. Is it possible to add these costs on the ...
3
votes
1answer
943 views

How to edit PostgreSQL geodatabase table using ArcPy?

I'm trying to update a table using a python script and it's not working. The table resides in a postgres geodatabase and is versioned. I have also verified my user permissions (I'm the admin). The ...
7
votes
1answer
2k views

What is difference between ST_DWithin and ST_Distance for proximity search in PostGIS?

I have records stored in a table with latitude/longitude coordinates stored in a geometry field. I want to find all records nearby a user supplied reference point. Note "nearby" probably means less ...
0
votes
0answers
64 views

Unable to set pg as default backend with osgeo4w GRASS

I am trying to set PostgreSQL as default backend for GRASS, but cannot manage to get it right. Working with GRASS 6.4.3 print g.run_command('db.connect', driver='pg', flags = 'c', ...
1
vote
4answers
68 views

postgresql how not to show the first result returned st_distance

I have a query that calculates the distance away from a given point, however currently my query returns the point I am actually querying a distance for. How would I prevent site 1 from showing in my ...
8
votes
1answer
230 views

What spatially-aware GUI administration softwares exist for PostgreSQL/PostGIS (free or paid)?

Most of my experience with PostgreSQL has been PGAdminIII and psql, but I'm interested to know what others use. Is there PostgreSQL equivalent to SQL Server Management Studio (SSMS) and its ability ...
2
votes
3answers
4k views

Unable to connect PostgreSQL to ArcMap 10.1

I'm trying to connect my local PostgreSQL database to ArcMap 10.1 but for some reason I'm unable to connect to the server. Any ideas of what might be causing the issue. For the instance option in ...
0
votes
1answer
311 views

Install both postgis and postgresql-9.1-postgis packages?

I've installed the PostgreSQL package (ver. 9.1) on a Debian 7 server using the command "apt-get install postgresql postgresql-contrib". I now want to install PostGIS as well. Should I install both ...
0
votes
0answers
58 views

SQL questions testing pgRouting 2.0

Following the post:pgRouting 2.0 for Windows quick guide, cuz I have some problem with DB manager(here is the problem I posted:DB manager in QGIS 2.0), I skip DB manager and save the test shapefile, ...
2
votes
1answer
245 views

DB manager in QGIS 2.0

I followed this post:pgRouting 2.0 for Windows quick guide, and I want to use DB manager to load the test data into my PostgreSQL database. I used url to get the test data: I have set up the ...
1
vote
1answer
111 views

Problem using pgRouting 2.0

I follow this great post:pgRouting 2.0 for Windows quick guide, and I have loaded the test data from GeoJSON, like this: But I don't know how to load this layer into my database, and my DB manager ...
0
votes
1answer
188 views

Visualizing information when click in polygon‏ - Spatial join

I am new to cartodb, right now I am trying to do something similar to the cartodb tutorial: Create your first map with CartoDB.js, but with polygons using my own map. For example: I did a spatial ...
1
vote
1answer
72 views

What is the complexity of neigborhood search in PostGIS?

I have a point with coordinates x and y. I want to know all the points in a square around this point, with side L. For this, in postgresql/PostGIS, I use: SELECT * FROM table_of_points WHERE ...
4
votes
1answer
216 views

Password protecting tables in cartodb

I am trying to find an easy way to have private tables, yet exposing them to certain clients if they are authenticated. This authentication can be achieved by a token system that allows the client to ...
2
votes
1answer
2k views

Geoserver sql view using an external sql query?

I am trying to figure out a way of producing a view of a layer in GeoServer, using an external sql query. By external I mean, for instance, a user defined query in a php from where all the parameters ...
0
votes
1answer
644 views

Maximum size on the bounding box with ST_MakeEnvelope and && and geography column in PostGIS?

I've done quite a few google searches, but haven't managed to find anything that has helped me directly. I've got a few features in a PostGIS table with a geography column. The features are focused ...
4
votes
1answer
228 views

ArcGIS Displays Wrong Date

I have a postgres database with a series of fields that includes a date field in MM/DD/YYYY format. Through ArcSDE it displays in Arc in a weird format with the wrong date and some made up time. When ...
1
vote
3answers
590 views

Table Relate Spatial and Tabular data in QGIS 2.0

I am self taught in GIS, so I apologize ahead of time for my noobness. In ArcGIS you have two options to associate data in a table to spatial data. Based upon my understanding, the first is Join ...
3
votes
1answer
979 views

osm2pgsql: Import data in custom projection

I have osm-file and I want to import it to the postgresql via osm2pgsql with custom projection EPSG:20022. I look to the help and found out any keys like -E EPSG: but when I try to do with it I get ...
2
votes
2answers
186 views

Merging contour lines along tile boundaries

I am working with the CANVEC data set. The data in canvec is broken up in to mapsheets. eg. 116B04. I have data covering the entire province of the Yukon. I have merged all the data in to one ...
3
votes
1answer
1k views

Import of OSM data into PostgreSQL using osm2pgsql: which formats are supported?

I followed a tutorial to install a PostgreSQL server on my Mac OS X Mavericks, and to import some OSM data with the osm2pgsql converter ...
5
votes
1answer
138 views

What maintenance should I do for PostGres database?

I'm getting familiarized with using PostGres for ArcGIS. I've only ever used SQL Server. I was successful in setting up an SDE database within PostGres 9.2, but I'm not sure what type of maintenance I ...
4
votes
1answer
2k views

postgis: difference with && operator and st_intersects

I found that there is a difference in result between using A && B operator or one of the geo functions like st_intersects(A,B) or st_overlaps(A,B) While the st_intersects or st_overlaps ...
11
votes
2answers
3k views

GUI tools for PostgreSQL/PostGIS?

as a cartographer I work with TileMill to visualize geodata. I installed a local PostgreSQL server with PostGIS and osm2pgsql. Everything works fine. Anyhow, I am new to working with servers and the ...
1
vote
1answer
3k views

Postgresql Trigger to insert records in my table from another one

I spent few days trying to resolve this question but it was impossible. I have two tables: Table1. CREATE TABLE point ( id_point integer NOT NULL DEFAULT, layer text, elevation numeric, ...
4
votes
0answers
500 views

Hibernate Spatial - 'Invalid endian flag value encountered' Exception

I'm trying to run a simple query in Hibernate Spatial 4.0 on PostgreSQL 9.3. I have a number of objects in a table with latitude/longitude values, and I'm trying to query objects that fall within a ...
1
vote
1answer
822 views

Postgis functionalities in Microsoft SQL Server

I'm using Postgresql 8.4, Postgis 1.5 for My GIS works. Planning to change my Database to Microsoft SQL Server 2012. Can we use all the Postgis functionalities in Microsoft SQL Server 2012. Please ...
0
votes
2answers
130 views

Problem loading view from postgres

My problem is loading view table from Postgres. Here is my view table: SELECT h1.gid, h1.st_buffer FROM vmo_buffer_2m h1 LEFT JOIN vodomjeri h2 ON st_intersects(h1.st_buffer, h2.geom) WHERE ...
2
votes
1answer
1k views

Postgis: find geometry within multiple polygons

I've got two tables with geometry in it. Table 1:items holds items with a point geometry; while the second table property_zones holds polygons. The Items: CREATE TABLE public.items ( id integer ...
0
votes
1answer
445 views

Defining a WHERE clause from a subquery for a query to create a handy view

It is about my implementation from an answer of this question. It is more an postgresql issue than a GIS issue, whereas it is about geometries and spatial queries. I want to create a view from the ...
1
vote
0answers
217 views

Upgrading Postgres database to Postgis 2.1.1

I executed the postgres 9.1.6 with Postgis 1.5.8 dump from a remote server on Linux pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/tumidb9.1geom1.5.8.backup" tumidb9 So I have a ...
2
votes
0answers
208 views

Styling river charts with TileMill - issue with filling a polygon made of disjoint line segments

I am trying to style inland river charts produced by the Army Corps of Engineers. I imported the vectors into Postgres, and I’m using TileMill to style the data. I am having trouble correctly filling ...
0
votes
1answer
623 views

ogr2ogr DXF to PostgreSQL some LINESTRING coordinates mirrored to the negative longitude

I have been using ogr2ogr GDAL tool to migrate geospatial data from DXF file to PotgresQL database without much problems. But now there is a special problem I can not fix. I have a DXF file with ...
1
vote
2answers
564 views

getting data into geoserver from two columns to form a point

I have a problem on how I am able to retrieve a geometry data from postgres using geoserver. I am new to this. The latitude and longitude are on separate columns. So I'm trying to figure this out by ...
-1
votes
1answer
492 views

Create a View with a new geometry field in PostGIS

I have a table in my data base. This table was created with differents fields as 'x','y' and 'z'. Now I need to make a view to generate a geometry column, but I found problems, because the created ...
1
vote
1answer
115 views

how can I assign an id column in sql query that merges geometries

I have a layer that consists of a number of features. Each features is a line segment and has an attribute named zone_name which takes one of 7 values. What I want to do is to use the atlas ...
0
votes
1answer
193 views

How to format SQL expressions for ArcPy with ArcSDE for PostgreSQL feature class?

I'm trying to use SelectLayerByAttribute to find the last point added to a FC. I figured I could use the objectid field because it's serial. I wrote the following: ...
1
vote
1answer
185 views

Clip geometries in table based upon value

I have defined a view in postgres that returns a set of polygons and can be queried as follows:- SELECT wkb_geometry, grade_count FROM ng_schema.grade_count_report [sql.where] order by grade_count ...
3
votes
2answers
942 views

How to connect Microstation V8i to Postgresql?

I have been searching the web trying to find out if any one has connect Microstation to Postgresql, and to my surprise there has not been alot of chatter on ways to do this.. When looking at the ...
1
vote
1answer
94 views

Google Map API and OS Opendata in QGIS

I'm new to gis and having problems with matching reference systems for my two data sources. I have downloaded some OS Vectormap data which I believe is in 27700.. And I have some longitude and ...
4
votes
2answers
9k views

Installing postgis on postgresql 9.1 not working

I'm trying to install Postgis 2.0 on a perfectly functionig postgresql 9.1.10 database. I installed the package via: richard@srv1:/$ sudo apt-get install postgresql-9.1-postgis postgis Reading ...
7
votes
1answer
925 views

Performance in calculating raster statistics in PostGIS

I'm trying to calculate raster statistics (min, max, mean) for each polygon in a vector layer using PostgreSQL/PostGIS. This GIS.SE answer describes how to do this, by calculating the intersection ...