We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
6
votes
2answers
419 views

Building spatialindex in Postgres with JNDI for GeoNetwork?

Which steps should be taken to build the spatialindex table inside PostgreSQL when using a JNDI connection? Is the table built by GeoNetwork or does it need to be created manually? What I have ...
6
votes
1answer
915 views

What is the best way to merge lots of small adjacents polygons ? (POSTGIS)

I am looking for a way to merge lots of small adjacents polygons. I have this kind of features: So if my polygons are adjacents AND have the same color attribute then I want to merge those polygons. ...
6
votes
2answers
474 views

What controls the direction of TopoGeometry Linestrings?

In PostGIS, what if anything controls the direction that topogeometry LINESTRINGs will have when converted to geometry? It doesn't appear to be the order in which the edges are listed in the call to ...
6
votes
1answer
1k views

How to create a buffer on a linestring with varying or increasing/decreasing width

I want to buffer rivers/streams for a topographic map. I'm using a PostgreSQL/PostGIS database. Usually, I create buffer with ST_Buffer with a constant width. For example, the width of a river buffer ...
6
votes
1answer
695 views

Maritime route prediction avoiding land masses

I am working on a project where the following functions have to be implemented: Predicting the locations of the ships (in maritime environment) at a future time. (Can be done with Kalman filter, IMM ...
6
votes
1answer
10k views

How to install PostGIS and pgrouting on Windows if Stack Builder fails to connect?

I know this has probably been covered and that there are plenty of guides out there, but for the life of me I cannot get pgrouting installed. All guides/how-tos are different or assume prior knowledge....
6
votes
1answer
1k 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 ...
6
votes
0answers
354 views

PostGIS issue with ST_Clip?

I'm trying to clip a raster using a polygon but I end up with a result that doesn't seem right and where some raster pixels are outside the polygon used for the clip. See image below where polygon is ...
6
votes
0answers
300 views

How to remove overlapping features by percentage?

I have geometry records of type MultiLineStringZM and I am trying to INSERT new records that overlap some of the records currently in the table. If they were type MultiPolygon I could use ST_Area() as ...
6
votes
1answer
470 views

Display pictures stored as bytea in postgresql column in qgis

does someone knows a possibility to visualize in a bytea column saved photos of a postgresql database in qgis? I would like to store pictures directly into the database, not just the link/ url.
6
votes
0answers
825 views

How to load the layer style from Postgresql with Python?

I would like to write a python macro that upon opening my QGIS project file will reload the default style for each layer from the postgres 'layer_style' table. I have had a look at the documentation ...
6
votes
0answers
864 views

What is the correct syntax for using gdal_rasterize with a PostGIS datasource [closed]

I have a multipolygon layer in PostGIS and I would like to use gdal_rasterize to generate a raster. I cannot find the correct syntax to specify PostGIS as the data source. $ gdal_rasterize -a ...
6
votes
0answers
2k views

Getting ST_Difference between two different tables in postgis [closed]

One table has a series of grid squares in it, and the other has millions of voronoi polygons in it. I need to create a table that has the difference between the two of the them so that I effectively ...
5
votes
5answers
3k views

“libintl-9.dll is missing” when trying to run PostGIS 2.0 Shapefile and DBF Loader Exporter

When I try to run PostGIS 2.0 Shapefile and DBF Loader Exporter I get the following error: The program can't start because libintl-9.dll is missing from your computer. Try reinstalling the ...
5
votes
2answers
12k views

Converting mulitlinestring to linestring in PostgreSQL with PostGIS? [duplicate]

I am using postgres with postgis and I have a set of points which I would like to interpolate with a set of lines. Now my problem is that the set of lines are of type multilinestring. Is there a ...
5
votes
2answers
2k views

Create custom coordinate system in postgis

I have a Wayne county Michigan parcel dataset with a custom SRID: when I bring it into ArcGIS Projected Coordinate System: NAD_1983_HARN_StatePlane_Michigan_South_FIPS_2113_Feet_Intl Projection: ...
5
votes
2answers
634 views

PostGIS ST_Distance vs. pgRouting

I am looking to create a city directory web app, and the site will need to be able to calculate distances between two geospatial points (like the distance between the user and a restaurant, or the ...
5
votes
1answer
2k views

PostGIS - ST_Within or ST_Disjoint performance issues

I have a table of about 150,000 points in PostGIS with a spatial index and a SRID of 27700 (OS). I want to select the points that fall outside England and Wales. I have a multipolygon table with ...
5
votes
1answer
5k views

Copy a spatial table from one PostGIS database to another PostGIS database

I have two PostGIS databases on two different servers. The first server is PostGIS 2.0 and the second server is PostGIS 1.5. On the PostGIS 2.0 I have a table "lakes" with MULTIPOLYGON geometry ...
5
votes
1answer
13k views

How to get the coordinate system from a PostGIS database?

My question is probably amazinlgy stupid, but I can't find the answer myself. I would like to extract from a database the latitude and longitude of points. For now, I'm getting x(the_geom) and y(...
5
votes
2answers
6k views

PostGIS: Closest polygon to point

I have .shp file of polygons and single (point) location. How can I retrieve the closest polygon to that single location?
5
votes
3answers
2k views

How to access qgis, postgis, etc in ubuntu through Amazon AWS

I'm new to programming; however i have been able to read and research the programs i need to set-up for a GIS system. I chose to use Geoserver (as WMS) Postgresql, postgis, Qgis and Openlayers. I am ...
5
votes
3answers
2k views

How to find out what features /tags are included in an .osm or .pbf file?

I would like to load osm data into a postgis (postgresql) database. In How to load OSM data into PostGIS preserving all Tags? there has been a solution posted: Download the OSM files (in .osm format)...
5
votes
1answer
250 views

Getting active PostgreSQL connection's username as variable in QGIS?

Is it possible to get login username from a current PostgreSQL layer connection in QGIS? My goal is to automatically populate new objects in a layer with an editor login username (not @...
5
votes
3answers
5k views

Simplify geometries but keep them contiguous

I have several detailed contiguous areas. I need to turn each one into KML, but when I do so, the end result is too large (in terms of memory). Using PostGIS I need to simplify these geometries but ...
5
votes
2answers
1k views

How to add sfcgal to an already postgis enabled database

I have a postgres 9.1 database with postgis 2.1, and I recently needed to add sfcgal to it, so i rebuilt postgis 2.1, but I can't find out how to update the postgis extension in postgres without ...
5
votes
2answers
11k views

How to find nearest line from point using PostGIS?

I have a table of Roads in PostGIS which geometry is "geometry(MultiLineString,4326)". Its attribute are gid osm_id name ref type oneway bridge maxspeed geom Now i want to know the ...
5
votes
1answer
5k views

How to compare geometry in PostGiS?

I have a two tables. 1 table have srid=4326 2 table have srid=70066 I want to compare geometry columns of this tables. I find function ST_Equals(geometry, geometry) but its not what i want. I need ...
5
votes
2answers
426 views

How to Change Vector Datasource in QGIS 3.2.0?

QGIS 2.18 has a very good function Change Vector Datasource: In QGIS 3.2.0 there is Update SQL layer but it doesn't work like Change Vector Datasource. When I open it, select and do update, it acts ...
5
votes
3answers
108 views

avg dist between points within polygons

I have a table of points (schools t1) and a table of small polygons (buildings t2). They are within large polygons (p) that represent districts. I am trying to get the average distance from a school ...
5
votes
1answer
6k views

Postgres/PostGIS calculate area of Polygon in Square Miles

Polygon in Postgres/PostGIS database. It has the spatial reference 4269 which is in degrees. I know there is a ST_Area() to calculate it. this is my query to select the areas in the SRID units. ...
5
votes
4answers
50k views

postgresql export database by command or pgAdmin III [closed]

I want to export my postgresql db with pgadmin. I search all of its GUI, I also searched the web and I don't find some sueful information. I know how to import the db into postgresql: create a db with ...
5
votes
1answer
16k views

Loading Shapefiles into PostGIS using shapefile import/export manager

I am a complete beginner with PostGIS. I've started learning it using the tutorials provided by Boundless. In section 4 of this tutorial where I want to import shapefile in PostGIS, I do not have the ...
5
votes
1answer
1k views

PostgreSQL: How to use more than one CPU?

I'm creating a real time traffic application. I read the AVL car data from a web service and insert the records in a PostgreSQL table. Then a trigger calculates the closest link and if there is a ...
5
votes
2answers
2k views

How to specify gdal_contour output format as postgresql table?

I want to generate contours with gdal_contour command. It generates an output as shapefile by default. I want this utility to output a postgresql (postgis) table. The documentation says there is a -f ...
5
votes
2answers
498 views

How to run (SQL QUERY) in a different schema in QGIS DB Manager?

My last question was How to QUERY (SQL) in QGIS DB Manager? The result was this: I was successful... But when I try to use the same layer in another SCHEMA (Not in Public) it gives me this error: ...
5
votes
1answer
3k views

How to update OSM data using osm2pgsql?

I have already loaded osm data into postgresql earlier. Now I want to update the database. I have downloaded the pbf file from geofabrik. How I can update the database without deleting the existing ...
5
votes
1answer
2k views

Find number of points within certain radius of a set of points

I have two tables. One is a list of stores (with lat/long). The other is a list of customer addresses (with lat/long). What I want is a query that will return the number of customers within a certain ...
5
votes
3answers
1k views

What does the values in column clazz (osm2po) mean?

I have a given table in postgreSQL with OSM data which is used for routing purposes in Java with PGRouting. There exist two columns which I don't understand. They belong to osm2po. They are labeled ...
5
votes
1answer
446 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 ...
5
votes
2answers
1k views

PostGIS stored procedures

I am wondering if there are open source applications that make extensive uses of the special functions (the ST_* stored procedures) provided by PostGIS? I found many apps that support storing map data ...
5
votes
2answers
1k views

OGR to read from oracle, and then write a CSV

I need to read an Oracle (not spatial) table with OGR. I have no problem with ogrinfo. I use this command: ogrinfo -ro OCI:user/password -sql "select * from HR.EMPLOYEES" I have this kind of ...
5
votes
1answer
243 views

PostGIS function to move a polygon to centre over new point coordinates

Background: (Note: I am trying to do as much of this as possible in PostGIS/PostgreSQL) I have a PostgreSQL table containing location data of an aircraft in orbit around a city. Every second a new ...
5
votes
1answer
103 views

PostGIS same query and same geom produce different output when writing hex or text formatted geom

the same geom(etry) once in hex code and the other one in text formatted produce in text formatted a linestring (which is correct!) and once a multilinestring when using the raw hex geom (which is ...
5
votes
1answer
558 views

Removing unwanted Linestrings from Multilinestring in Postgis

I have a Multilinestring layer and I have noticed that in some of my geometries there are unwanted Linestrings. They are composed of two points (start and end) but both are the exact same points. That ...
5
votes
2answers
534 views

Filter data with PostgreSQL or geoserver?

I'm working on a PostgreSQL database with PostGIS. I need to filter an 8 million records table in order to publish a view through geoserver. I know two ways to do that, one is that I create view in ...
5
votes
4answers
2k views

Debugging ogr2ogr “AddGeometryColumn failed” and “Terminating translation prematurely after failed translation of layer”

As noted in the title, I'm getting the following error: ERROR 1: AddGeometryColumn failed for layer pretty_polys, layer creation has failed. ERROR 1: Terminating translation prematurely after failed ...
5
votes
1answer
910 views

PostgreSQL : total length of roads in polygon

I have 2 tables : wgo (lines) and wbn (polygons) With this code, I can display the length of each road inside a polygon. If I have a polygon with 2 roads in it, as a result I get 2 rows with the 2 ...
5
votes
2answers
911 views

Computing radius of gyration in PostGIS

The radius of gyration is a measure of the standard deviation of distances between points (and their center of mass), i.e., how frequently and how far points are moving. A formal definition is: I ...
5
votes
2answers
12k 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 ...