PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
2answers
31 views

How can I speed up these queries

I am trying to make some calculations using PostGIS and PostgreSQL. There are 3 important tables : wbn, wgo and wvb. These tables have about 500.000 objects in them (geometries). The problem is that ...
0
votes
0answers
9 views

Focal Mean of neighbors using ST_DWithin on points in PostGIS

I'm trying to calculate focal means on a grid of points in PostGIS. I know that a raster calculation would be more efficient, but I only need to run the algorithm once to get the data, so I don't ...
1
vote
0answers
12 views

PostGIS 3D Extrude function

I selected buildings from OSM (which represents my city). I insterted a new column 'height' and I filled with a random number exactly number 2, so every building have 2metres altitude. Buildings are ...
2
votes
1answer
111 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 ...
1
vote
1answer
64 views

What index to use in this situation

I am working with 3 tables with 3 x 500.000 rows. There is one colomn that gives each object a unique ID (UIDN), there also is a colomn with the geo data (the_geom). There also are some other columns ...
1
vote
2answers
261 views

Function for Selecting Points in a Polygon

I've been banging on this for a few hours, but since I'm relatively new to PostgreSQL and PostGIS, I can't find the solution. I'm trying to create a stored procedure (function) which will return all ...
3
votes
1answer
36 views

MS SQL Server 2012 to Postgresql

How to use MS SQL Server 2012 spatial database in postgresql using foreign data wrapper?
0
votes
1answer
272 views

SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid'); not work

I'm using using OSgeo 6.5 dvd and studing pgrouting workshop (link) for get shortest path. I created network topology call "pgrouting - workshop". There are three commands to execute in section 5.2 ...
1
vote
0answers
33 views

Raster slightly displaced after exporting from PostGIS database

With the following queries I've exported a raster (GTiff) from my postgis database: 1.Creating a large object: WITH buffer AS ( SELECT id, ST_Buffer(geom,50) AS geom FROM ...
3
votes
1answer
171 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
25 views

3D functions with SFCGAL in PostGIS

I have this setup: "POSTGIS="2.1.2 r12389" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER" on ...
1
vote
2answers
33 views

What is the best way to take a WKB value in SQL Server Management Studio and create a PostGIS geometry in PGAdmin III?

Normally, I would just use WKT strings but I'm investigating some possible geometry weirdness with the Text methods in SQL Server. I would like to copy/paste the WKB values from the SQL Server ...
1
vote
1answer
99 views

case insesitive CQL-openlayers filter

I have the following filter in openlayers 2.13 var filter_c = new OpenLayers.Filter.Comparison({ type: OpenLayers.Filter.Comparison.LIKE, property: 'p_name', value: ...
1
vote
0answers
36 views
+50

Postgresql JDBC Table Valued Parameters

MSSQL has a great feature called Table Valued Parameters. It allows you to pass a table of a custom data to stored procedures and functions. I was wondering what is the equivalent , if exists, in ...
1
vote
0answers
45 views

How to save a google map direction into a postgresql/postgis database?

I have taken a direction(route) from google map. Can I save this resultant direction/path/route to my postgresql/postgis database? How?
2
votes
2answers
37 views

Do I still have to use a template DB for PostGIS?

createdb mydb -T template_postgis Is this -T template_postgis still necessary in new versions of PostGIS? I never saw this db being different, it always has those 3 sql files you run there. Is this ...
1
vote
1answer
21 views

convert XML to PostgreSQL using MATLAB

In a weeks time I should be able to transfer the GIS data provided in the XML format to PostgreSQL database. I have prepared the PostgreSQL database (empty) and looking at Matlab scripts. (I am on ...
3
votes
1answer
77 views

Python Postgres Multiprocessing speed increase

I'm trying to speed up my PostGIS queries using multiprocessing. My current setup is using python and psycopg2 such as below. Although this has given a speed increase, it still seems like there are ...
3
votes
1answer
689 views

How to use the pgRouting TSP function?

For more than two days I'm trying to make the TSP algorithm work in PostgreSQL/PostGIS. My problem seems to occur when I'm trying to take into account the road network for TSP algorithm. I know that ...
1
vote
0answers
41 views

Display postGis query result on Google maps Api [closed]

I have MULTILINESTRING Geometry in Postgis and as Google Api does not provide any way to plot MULTILINESTRING , i'm converting MUltiLINEstring to LINESTRING through ST_Merge function. But when i'm ...
3
votes
1answer
22 views

Update/refresh edited PostGIS table in QGIS

I imported a PostGIS table (with geometry) from my PostgreSQL database as a layer in QGIS. Later, I edited the table using PostgreSQL UPDATE commands. Is there a way to quickly update (like a refresh ...
1
vote
0answers
16 views

qGIS' Database Manager “Load as New Layer” Option Not Working

I'm trying to visualize some of my database queries on qGIS. I run a valid SQL query which returns a subset of my table that should be easily loaded as a layer. I then check "Load as new layer," ...
5
votes
0answers
63 views

How to compare two Geodatabases

I would like to compare the data of two geodatabases, in SDE or a file geodatabase, to detect the changes occured between them , i've tried to compare the Xml workspace document of both databases ...
3
votes
1answer
283 views

How can I make a Postgres/PostGIS ST_Distance_sphere query more efficient?

Within Postgres/PostGIS I have written an 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
24 views

pass an array of geometry to postgresql function

I am trying to pass an array of geometry to a postgis function using JDBC with JAVA: CallableStatement proc = conn.prepareCall("{ ? = call somefunc(?) }"); ...
0
votes
0answers
22 views

OpenStreetMap Road and Polygon Data as 1x1 Degree Shapefiles

I am working on a global project for which I want to use OSM data as a model input. I am looking for advice on the best way to produce 1x1 degree WGS84 shapefiles from either regional extracts or the ...
-1
votes
0answers
25 views

Postgres localhost -->ip adress [closed]

got to some connections in local network and i have a problem changing localhost at postgre server to host from the stations IP adress, can anybody explain in some steps what operations need to be ...
1
vote
2answers
41 views

Getting OSM maps in Equirectangular Projection via osm2pgsql

I'm trying to import some OSM data into psql as equirectangular projection using osm2pgsql with the following command: osm2pgsql -c -d osm -S /usr/local/share/osm2pgsql/default.style -l ...
1
vote
1answer
211 views

pgrouting shortest path result in wrong order

I am using the Dijkstra shortest path algorithm to plot a route between two points using the latest version of pgrouting in postgresql 9.1 with postGIS 2.0.1. I am able to get a correct result ...
3
votes
3answers
190 views

osm2pgsql --hstore

I'm working with postgreSQL 8.$ and PostGIS and I need to import osm file to database. I tried it with utility osm2pgsql, but I have problem with hstore extension. Does anyone know how to solve this ...
0
votes
0answers
12 views

Geoserver WPS - Tiled Barnes Surface with geospatial index

I'm using Barnes Surface to compute a set of points from my PostgreSQL db. The problem is that my whole table is processed for each tile. I added a geospatial index on my points, how can I tell ...
0
votes
1answer
30 views

PostGIS transform geometry with long crs

How do i transform a geometry to a new geometry with a crs like urn:ogc:def:crs:EPSG::25832 st_transform(geom,integer srid) obviously doesn't work since my srid is a string found a solution ...
1
vote
1answer
45 views

Routing in MS SQL 2012

I would like to perform routing in MS SQL 2012 similar to pgRouting. In pgRouting, I'd do the following steps to create a routable network: Create a database routing with template template_routing ...
0
votes
0answers
13 views

why is the CDB_QuantileBins function not found in cartoDB?

What is wrong with the following SQL query on my data? I found pretty clear documentation (1,2) that the function is supposed to work. SELECT CDB_QuantileBins(array_agg(numeric_column_of_mine), 10) ...
2
votes
1answer
46 views

polygons and lines not drawn correctly (qgis+postgis)

i am really new to this so i could use some help.. i load osm file into postgresql and then i connect to the database with qgis t to plot it and i see this it happens to whatever file i import to ...
1
vote
0answers
17 views

Problem installing Aggregate Polygons Function in PostgreSQL 9.3

I'm new to PostgreSQL and I've been trying to run a sql function written by Dr. Horst Duester, found in http://www.kappasys.ch/cms/index.php?id=65&L=5, which is used to aggregate detached ...
0
votes
1answer
73 views

Why is ArcMap leaving connections “<IDLE> in transaction”?

The TL;DR version: When you use ArcMap Query Layers to access a PostgreSQL table, ArcMap's database connections are almost always sitting idle in unclosed transactions. Why would this be? I have ...
0
votes
1answer
24 views

how to run a PostgreSQL query on CartoDB tables?

I want to run a VACUUM FULL on my table, but I don't think it's working. Simply sandorl.cartodb.com/api/v2/sql?q=VACUUM&api_key={MYKEY} results in long output, apparently only repeating for ...
2
votes
1answer
80 views

System requirement specification for Big Data Postgesql, PostGIS, and GeoServer based service

I want to start a GIS portal for around 3000-5000 visitors per day. MY database will be of an approximate size of 10 Terrabytes. The maps and layers are moderately complex, satellite-map overlay is ...
3
votes
1answer
96 views

User locking postgres/postigs table without privileges

I have a PostgreSQL 8.4.4 database with PostGIS 1.4.2 and I am experiencing a problem where an external GIS system (using embedded CadCorp SIS 7.0) is connecting to the database and taking Access ...
0
votes
1answer
50 views

How to save results of postgresql to csv/excel file using psycopg2?

I use driving_distance in postgresql to find distances between all nodes, and here's my python script in pyscripter, import sys #set up psycopg2 environment import psycopg2 #driving_distance module ...
0
votes
1answer
40 views

Use psycopg2 to do loop in postgresql

I use postgresql 8.4 to route a river network, and I want to use psycopg2 to loop through all data points in my river network. #set up python and postgresql connection import psycopg2 query = """ ...
0
votes
2answers
189 views

I have a database in PostGIS, is possible using CartoDB? or I need another map server?

I have a data base in PostgreSQL/PostGIS, and I want to offer to my users a way of visualize it, Is it CartoDB the best option? It is possible? Or which is the best option around? (Please provide ...
1
vote
0answers
35 views

Geoserver WFS Fails to Load in OpenLayers

I'm working with Postgis->Geoserver->openlayers on localhost. I followed a Geoserver tutorial to import some nyc sample data into my postgres/postgis database and link it to Geoserver. The layer ...
0
votes
0answers
25 views

PostGIS: calculate width/length of rectangular surface

I have rectangular shaped surfaces (multipolylines) and I need to calculate the width and/or length.. they have a rectangular shape but they're not real rectangles. Is there a way to do this ?
0
votes
1answer
35 views

Increase Map Availability in Geoserver [duplicate]

I have an application which access same map from two geoserver using loadbalancing concept the map is accessed using WMS service. where the two geoserver connected to a single same POSTGIS database. ...
0
votes
0answers
7 views

PostgreSQL : send all rows of a table to a function [migrated]

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, ...
2
votes
1answer
229 views

Open Failed - Attribute Column Not Found

I'm running a PostgreSQL database on my local machine and have set up an connection to it from ArcMap 10.1. I can see all of my tables in the Catalog pane. There is one table that I cannot seem to ...
1
vote
1answer
88 views

How to execute sql lines in postgresql using python?

I use postgresql 8.4 to route a river network, the following is my code typed in pgAdmin and the result is fine, select * from driving_distance ('select gid as id, start_id::int4 as source, ...
1
vote
2answers
199 views

Start postgresql+postgis without installation on Windows

I have used Windows7x64 and want start PostgreSQL+POSTGIS without installation process. I copy PostgresSQl\9.1 folder from other laptop to c:\opt. I need create .bat file with enviroment variables and ...