Tag Info

Hot answers tagged

9

It's been a while since this question was updated, so briefly ArcGIS 10.1 and ArcGIS 10.2 both natively support PostGreSQL and PostGIS data types. Included in the help for both versions is a walkthrough of gettting set up, and configuring tables to use the PostGIS geometry types.


8

Use ST_Azimuth to get the angle from the origin point to the point of interest. I've used simple geometry points here since you didn't have any sample data, but you're probably working with geography. The principle is the same: WITH points(star) AS (VALUES (point(0.5, 1)), (point(0.2,0.2)), (point(0.2, 1)), (point(0.8, 0.2)) ) SELECT star FROM ...


8

I would recommend using pg_dump and pg_restore which work very well in exporting data from one database and then restoring it to another database. there is a nice tutorial here http://www.mkyong.com/database/backup-restore-database-in-postgresql-pg_dumppg_restore/ But simply you will want to export using a command like pg_dump -U username databasename ...


8

you have to use the union function like this SELECT att1, st_centroid(st_union(geom)) as geom FROM schema.table GROUP BY att1; so you can obtain centroid of point that have same attribute.


7

This annoying issue occurs because libgdal was forked between libgdal1 and libgdal1h last year. Ubuntu GIS stable uses libgdal or libgdal1, Ubuntugis unstable uses libgdal1h. GDAL is a translator library for raster geospatial data formats. As a library, it presents a single abstract data model to the calling application for all supported formats. The ...


7

Assuming your data table look like: streetid housenumber 100 12 100 15 101 12 101 18 You could do a basic SQL query: SELECT streetid, count(*) FROM tablename GROUP BY streetid Where this might get more GIS-y and interesting is the case where streets with the same ID are not contiguous. You didn't mention if this is a ...


7

Why use a database? Because it's not necessarily the case, especially with larger datasets, that you can expect to be able to push the entire thing to the client. If you're talking thousands of points, then sure, but for millions of points you probably don't want each and every one represented in RAM on your end users' client. Not everyone has a super fast ...


7

ST_Distance actually calculates the distance between all the pairs of points, so, as such, no index could be used. So your query will do a sequence scan and then choose those geometries that are less than the distance you specify away. You are looking for ST_DWithin, which does use an index. SELECT SUM(population) FROM points WHERE ST_DWithin(location, ...


6

You may try this: DMS2DD for PostGIS EDIT Presuming this is your PostGIS table, running the DMS2DD function gives this: EDIT 2 Because you have only Degrees and Minutes, in the DMS2DD function you need to comment one single line to achieve your results:


6

The problem is that you've got the wrong coordinate order. WKT for SRID 4326 is longitude then latitude (think Cartesian, it is X then Y). The error is telling you that -122 is not a valid latitude. The geometry (or geography) needs to look like: SELECT ST_GeographyFromText('SRID=4326;POINT(-122 37)')... SELECT ST_GeographyFromText('SRID=4326;POINT(2 ...


6

Although someone else with more experience than I may be able to answer better, this is my understanding: To edit data in a spatial database, you need ArcGIS Server (the SDE components) That database can be anything, PostGIS, Oracle or SQL Server You can use native geometry types in the database, which should mean you can edit/view with other software. ...


6

The table gt_pk_metadata is an optional table that GeoTools (and GeoServer) use to work out what the primary key columns in a view are. It is needed to generate consistent feature IDs (FIDS) otherwise GeoTools will use the feature's java ID which will change from run to run. It is explained in this document. So you can ignore this error if you don't care ...


6

If you are working on your workstation it's more a matter of taste. Knowing how to use psql is useful for some situations like running sql scripts from files, pipe it with other tools, etc. It depends on your needs. My everyday work is done using pgAdmin and I only go down to the CLI when needed. On the other hand psql is sometimes your only option when ...


6

The pgShapeloader tool has the ability to upload a shapefile in a different schema than public. Just double click the public word and type your schema name instead:


6

There's two things going on here: the GIST API in PostgreSQL and the bindings of types to that API for the purposes of building an R-Tree. PostGIS necessarily uses the PostgreSQL GIST API. That's what it's for. That way we don't have to worry about transaction management or writing things to disk or all the other messy important things involved in ...


6

I am answering my own question with a proposed query. select *, ABS(x_permit-x_station)+ABS(y_permit-y_station) as manhattan FROM (SELECT longname AS NAME, lines AS metadata, T .slug, ST_Distance ( T .geom, ST_Transform (P .geometry, 3435) ) AS distance, ST_X(ST_Transform(p.geometry, 3435)) as x_permit, ST_Y(ST_Transform(p.geometry, 3435)) as ...


6

ERROR: **function addGeometrycolumn**(unknown, unknown, unknown, unknown,unknown, integer) does not exist It seems that PostGIS is not yet installed. PostGIS is an extension of Postgres which allows the use of geographic files. Install it and your import will work fine.


6

A really easy, but not fantastic measure is to get the Hausdorff distance between each combination, which is done with the ST_HausdorffDistance function. Using approximate LineStrings from your figure, these are all shown in blue, and the Hausdorff distance is shown for one of the pairs of lines in red: And the query to sort the 6 combinations in ...


5

I just had the same problem on Ubuntu Server 14.04. I installed postgis extension from the official ubuntu repositories. So I used # apt-get install postgis. Then, find /usr -name postgis.control didn't return any result. The reason was extension/postgis.control wasn't installed because of postgis-scripts wasn't. $ aptitude search postgis i ...


5

Assuming the given bounding box limits are in the same spatial reference system as the stored coordinates, and you know which spatial operator (intersects or contained by) you need: SELECT * FROM my_table WHERE coordinates && -- intersects, gets more rows -- CHOOSE ONLY THE @ -- contained by, gets fewer rows -- ONE YOU NEED! ...


5

I think it is better to store your points as geometries because you can use a spatial index for speeding up queries. The query: WITH points AS (SELECT ST_SetSRID(ST_MakePoint(x,y), 4326) AS geom FROM your_xy_table ), centroid AS (SELECT ST_Centroid(ST_Union(points.geom)) AS geom FROM points), multiobject AS (SELECT ...


5

When you spatially-enable a PostGIS database, the relevant functions, SRS table, and views are placed in the public schema, as you state. That does not mean that all or any of your own spatial tables need to be in the same public schema. PostGIS will still work on all spatial data in the "new" schemas. In fact, I usually place my application-specific tables ...


5

You no longer need to use the templates or even the script in the new versions of Postgis. As you can see in the Postgis - installation page, all you need is run the CREATE EXTENTION command. -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION ...


5

Your SQL query is missing one function. Add ST_GeomFromText and it will work. SELECT ST_AsText(ST_Centroid(ST_GeomFromText(('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )')))); Result: POINT(2.307692 3.307692) If you have a point table "mypoints" the corresponding query is select ...


5

First of all I suggest to use postgis function ST_SimplifyPreserveTopology instead of qgis semplification. If I understand it right you want to have a small file with an high level of detail... and thats impossibile (if you still want to use shape files). But you can do what other webgis do. You can create 2 or more shape files with different semplification ...


5

And, after a bit more reading the ogr2ogr help I found the answer more easily than I expected. ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb -sql "SELECT * FROM table" -dialect spatialite -nln new_table EDIT: As suggested by user30184 in the comments a cleaner, simpler method is: ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb ...


5

Since you're not worried about simultaneous edits on features, I'd say that in theory you have nothing to worry about. The main danger w/ QGIS is that simultaneous editors can stomp on each other's edits without noticing ("last edit wins"). For data under active editing with multiple users you might want to at least keep track of history, which you can do ...


5

If you don't have an attribute, you should first find a polygon feature class with the boundaries of Greece (e.g. on gadm.org). Then you have two solutions : You want the roads to be cut at the boundaries of you area of interest : use some clip tools (in QGIS : Vector -> Geoprocessing -> Clip ) You want to keep the two sides of a road when it crosses a ...


5

Your data looks like it's Mercator (big negative X's). I'll assume web mercator, but that could be wrong, and may lead to meter-level inaccuracies (if it's "real" mercator, use 3395). shp2pgsql -c -d -D -W LATIN1 -s 3857 -I 'path/to/shapefile.shp' my_table | psql -d mydb Now flip the coordinates to geographics inside the database ALTER TABLE my_table ...


4

Since you have PostGIS 2.1.1 you're ahead of the game. Make sure you have wget installed, it is what will download the data from the Census FTP site. Create a gisdata directory with: sudo mkdir /gisdata Use chown and chgrp commands to change the ownership and group of /gisdata so that your normal user can read and write to /gisdata. Start psql and ...



Only top voted, non community-wiki answers of a minimum length are eligible