PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
52 views

Finding out the extent of the default zoom levels of open layers

I'd like to reproduce the zoom levels of openlayers in postgis. I am not used to working with Javascript and am having difficulties to find out the exact extent in meters of the default zoom levels of ...
0
votes
1answer
80 views

function st_length3d(geometry) does not exist

Whenever I execute this query: SELECT ST_Length3D(ST_GeomFromEWKT('LINESTRING(0 0 0,5 0 3,5 10 5)')); Postgresql is throwing an error: ERROR: function st_length3d(geometry) does not exist SQL ...
2
votes
1answer
89 views

Load style from database in QGIS

Maybe it's a stupid question, but I don't know how to connect layer style with database in QGIS. When I save the style to postgres database in QGIS, it automatically saves the style in the table ...
2
votes
0answers
60 views

Error on qGIS with postgresql views

I have a PostGIS database with various tables and views. With pgAdmin I can make requests on the tables, on the views, all is working. I add tables in QGIS 2.8, it's working, I can read, write, modify ...
1
vote
0answers
280 views

PostGIS is already installed in schema 'public', uninstall it first

I am having problems adding postgis to my database. I'm following the instructions from here: http://postgis.net/docs/postgis_installation.html#idp32328352 (That said, I have combined some previous ...
1
vote
1answer
171 views

Using st_distance to calculate shortest distance between a table with point geometry to a table with multipolygon geometry

I'm trying and failing to do a calculation of the shortest distance for each point in a point geometry table (15000 records) to the nearest polygon edge in another table (170000 records). The end ...
0
votes
1answer
182 views

How to check overlap between two sets of Linestrings using postgis

I have 2 sets of LINESTRINGS, each set containing around 50 LINESTRINGS(which form a road), how do I check whether these two sets overlap or not ? Thanks in advance.
1
vote
0answers
74 views

ogr2ogr *.tab to postgres: Failng on date format

I'm importing some MapInfo tab files into a PostgreSQL database running the following command in FWTools Shell: ogr2ogr -f "PostgreSQL" PG:"dbname='<the_database>' host='<the_host>' ...
1
vote
1answer
63 views

how can I maintain curves when converting from geodatabase to postgresql using og2gui

I am using the ogr2gui to convert a feature class in a geodatabase to a postgresql feature class. My original data is in a file geodatabase, and I am using the folder OpenFileGDB format as my input ...
0
votes
1answer
154 views

Problem using raster2pgsql to upload a raster to PostGIS

I am trying to upload a large number of 3 band rasters to a PostGIS database using raster2pgsql on the command line in Windows 7. Here is my command: raster2pgsql -I -C -s 27700 -F -t 100x100 -M ...
2
votes
1answer
36 views

Accessing non-PostGIS Postgres instances from QGIS

Does anyone know if it's possible to query tables that are regular Postgres and not PostGIS with QGIS? I have a PostGIS database but want to access tables that aren't spacial - much like reading in a ...
1
vote
0answers
41 views

ST_Intersects does not use spatial index

I wrote this part of code: SELECT CB.id_2 AS CountyID, CA.id_2 AS NeighbourCountyID FROM "Counties10B" CB,"Counties10" CA WHERE ST_Intersects(CA.geog,CB.geog); But it does not use spatial index. ...
1
vote
1answer
84 views

Separating disjoint GPS path segments in Postgres

This involves a lot of code; if it's better suited for StackOverflow, please let me know! I'm working on a project involving trips that are constructed from GPS coordinates. I'm mapping the recorded ...
2
votes
1answer
186 views

Offline Map with mapnik: Tiles are vertically unaligned and randomly displayed (Leaflet + Tilecache + Mapnik + Postgis)

I'm new to maps and currently I am developing my first offline Map with OSM data. I have map data added to postgres via osm2pgsql and installed mapnik 2.2. For caching I have used tilecache with ...
2
votes
1answer
241 views

Calculate road distance between GPS points

I am working on tracking system that track location of vehicles on the map and tell their total distance from the beginning till today. I have device_id, timestamp and GPS location in the table like ...
4
votes
0answers
76 views

How to load layers from postgres using SELECT statement?

I am trying to load a postgres table (columns extracted using a select statement) from the python console in QGis: The following works fine and the layer loads as expected: uri = QgsDataSourceURI() ...
0
votes
0answers
36 views

Regarding Geoserver postgis Store - connection Issue

I have installed geoserver 2.7 in windows 8.1 Everything works fine but while I try to connect PostGIS store using the GeoServer web admin page I get error as unable to obtain connection. I have my ...
1
vote
2answers
90 views

PostGIS query to retrieve the largest polygon for multi-polygons by grouping on attribute field

I'm working with the Natural Earth 10m Admin 1 States Provinces polygon data. This dataset contains multi-polygons for each state / province in the entire world. What I'd like to do is to grab the ...
1
vote
1answer
99 views

Creating geodatabase administrator account other than SDE?

I need to create another account other than SDE and make it geodatabase administrator. Is it possible? I have already created another account and have given SUPERUSER priviledge. However I cannot do ...
0
votes
0answers
76 views

Copying File Geodatabase to PostgreSQL using ogr2ogr?

I am trying to export Feature Classes and Tables from GDB to PostgreSQL. I am using ogr2ogr tool to export from GDB to PostgreSQL. I am using following method: ogr2ogr -f "PostgreSQL" ...
2
votes
1answer
109 views

How to update a postgres/postgis table periodically with multiple data?

I have created a postgis table from point.shp with 5 fields containing 20 records. Now I want to populate the table with another 150 records keeping the previous 20 records so that the total records ...
2
votes
1answer
318 views

How can I generate a specific number of random points in a polygon with PostGIS?

I have a postgis table that I've uploaded a shapefile to, this shapefile contains 200+ polygon rows with several integer columns. What I'd like to do is similar to the QGIS 'random points in polygon' ...
0
votes
0answers
60 views

Postgres PostGIS geocode_intersection()- How to handle exception?

I have a table in Postgres with a set of records that are street names which needs to be geocoded to get an intersection point.Some of the records would be bad input and when using the ...
1
vote
0answers
19 views

Import postgis layer from S3 link

I have a postgis layer (layer.sql), which I am trying to add to my postgres database (mydatabase). The postgis layer is stored on S3 (https://s3.us.../layer.sql). I can add the layer to my database ...
0
votes
1answer
47 views

from two tables make a new one

I have two tables both of them has linestring geometry. I try to make a new one which contain the first one and from the second take only the decimal v when the linestring of the first table touch a ...
1
vote
1answer
141 views

PostGIS Clustering. Group by geographic point location proximity

I have a table where i keep user's location history. The system gets user's current location each minute and writes it into "user_locations" table. Later on; when showing the locations that the user ...
2
votes
2answers
69 views

Combining ST_Touches and ST_Buffer

I'm looking for a PostGIS/Postgres query thats a bit tricky. I want to find all polygons within a specified radius and all neighboring polygons to the polygon where I have my coordinates SELECT * ...
0
votes
0answers
196 views

Use postgis functions in restful web service java

I am trying to access Postgis functions in Restful web service JAVA. I am using netbeans 8.0.2, Glass Fish, J2EE 7 to create web service, PostGres 9.2 and postgis 2.1.1. Connection to postgres is ...
6
votes
2answers
449 views

How to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons?

EDIT: The trigger functions posted are well done but there are still a few issues. I've uploaded two screen recordings: http://workupload.com/file/ygRnYtp9 http://workupload.com/file/OBY7i9Fh I ...
1
vote
2answers
252 views

How to create a bounding box around a point using postgis

How to create a bounding box around a geom (POINT) and find all the intersecting geoms(POINTS) within that bounding box? Thanks in Advance
1
vote
2answers
53 views

Getting year from a field of type character varying

I did an intersection between two tables and the resulting field has an sdate of type character varying (29). An example value is 2012-01-13 05:00:00. How do I get just the year from it? I want to ...
1
vote
1answer
91 views

PostgreSQL 'forgot' PostGIS functionality

System details: PostgreSQL 9.3.5 64bit on Windows 7 Ultimate; PostGIS 2.1.3 Two days ago I re-organised my schema structure - effectively moving a series of tables of the form ...
1
vote
2answers
319 views

Intersection between line & polygon in PostGIS?

I have a linestrings consist of 2 vertices. I want to get all lines that don't intersects with any polygons on the way. In my example: blue line Not intersects - only touches some polygons, but black ...
3
votes
1answer
111 views

Adding vertices to polygon in PostGIS?

For example I have rectangular polygon (4 vertices). I want to add mid-point on each line of the polygon, so in total now I have 8 vertices. And from them I want to build new polygon who will have ...
0
votes
1answer
339 views

Configure GeoNetwork with postgreSQL

For testing purposes, I deployed geonetwork 3.0.1 on tomcat7 and ubuntu 14.04 with default configuration, especially h2 database. Now I wanted to switch to postgres and followed the instructions ...
1
vote
0answers
24 views

access the encrypted table in qgis

I have a vector layer in qgis connected to PostgreSQL database table. Suppose I encrypt the table column using pgp_sysm_encrypt or some other encryption in PostgreSQL Is there any way i can say ...
3
votes
2answers
85 views

ST_Area of a buffer differs by coordinate

Why are these results different? The only difference is the coordinate. Seem like the area should be the same. select st_area(st_buffer(st_setsrid(st_point(-70, 40), 4326)::geography, 10)) AS ...
1
vote
1answer
56 views

How to transform from a SRID to another?

How can I transform a geometric column of table from "SRID=4326" to "SRID=32650". I saw "ST_Transfom" and " ST_SETSRID" operator before. To do so, I used follow construction operator, But it dosen't ...
-1
votes
2answers
145 views

Sql Query in Postgresql

I am trying to execute a query but I am stuck. I have two tables A and B. The a table has columns gid, geometry(line-string) and value which cells is empty , the B table has got 3 columns gid, ...
0
votes
1answer
41 views

How to identify the right road using postgres and postgis

I want to identify a road at particular point of intrest using post gis. Example Input String : 16th cross near xyz mall. Problem I am facing As there will be many "16th cross roads" in the map , ...
1
vote
1answer
748 views

How to resolve the postgis “type geometry does not exist” issue on installing tiger_geocode extension?

I have installed postgis extension in my db. I have few columns associated with its type in few tables and now I would like to use reverse geocode functionality in my app. Hence I tried to create ...
1
vote
1answer
59 views

How to merge(transfer) one layer into another postgis layer?

I want to merge 2 layer which have identical structure. First layer is from postgres database table say "layer1" and other layer is shapefile say "layer2". I want to merge layer2 "INTO" layer1 (NOT ...
0
votes
1answer
121 views

PostGIS: Closest outside point to point inside polygon

There is the famous ST_ClosestPoint() return the closest point inside polygon to some outside point. I'm looking the inverse problem: I have a location inside polygon, and I want to get the closest ...
0
votes
0answers
43 views

PostGIS: Lines from location touches but not intersecting polygons

I have OSM .shp file of buildings. I created vertices along each line of the building in that way: vertices (red) & mid points (green). The mid points made by ST_Line_Interpolate_Point() after ...
0
votes
1answer
56 views

How to use intersection in spatial guery in postgresql?

I want to get id, time and point_metric columns from intersection between two geometry(geom_lines and gem_houses). I wrote following sql code, however it fore an error (argument of WHERE must be type ...
0
votes
1answer
36 views

How to set srid of a single point coordinates pair?

I think is better explain with a small example: selected a geometry point i get it back in a coordinate pair SELECT ST_AsGeoJSON('010100002031BF0D009A999919F9BD2D41CDCCCC4C758E5241') --result: ...
1
vote
1answer
185 views

counting overlapping polygons - ST_Union very slow, how to make it more efficient?

I have a set of 1000 polygons that overlap and I want to count the areas of overlap. I am using this blog post's instructions to create multilines from the 1000 polygons then use this line file to ...
0
votes
2answers
409 views

PostGIS: Closest polygon to point [duplicate]

I have .shp file of polygons and single (point) location. How can I retrieve the closest polygon to that single location? Thanks,
0
votes
1answer
37 views

Which is the correct Options 's “Hydraulics” field in EPANET?

Which parameter has to be given into Options 's "Hydraulics" field in EPANET? Has it to be only a name or a complete file name with the extension? In my case .rpt doesn't work, neither without the ...
1
vote
0answers
36 views

How raster files are stored in databases?

When storing raster files in PostgreSQL only one row is created in the database, but the raster is a matrix so typically it must create rows and columns. I want to understand how raster files are ...