Tag Info

Hot answers tagged

15

A great solution is to use QGIS as you gui for PostGIS. QGIS is a fully-functional GIS (I prefer to think of it actually as a meta-GIS given that it is built on many GIS packages). It has built-in support to connect to PostGIS and therefore gives you all the tools tou need to view, edit and create maps from your data.


8

ArcSDE uses 'struct tm' to pass date fields, so it certainly has the range to support the information. I suspect, however, that you're not using the supported date type (TIMESTAMP WITHOUT ZONE), which is causing the date to get mangled.


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

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.


7

You could accomplish this a few different ways depending on what sort of output you are wanting, but the concept is the same. It's generally easier to do a simple rotation followed by a translation rather than trying to calculate the coordinates in a single step. In this case, the basic steps are: Create a line of the desired length at the origin (0,0). ...


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 ...


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

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 ...


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

Ok, I'm assuming your Table is built is like this: Create TABLE ObsData (Type Text, X smallint, Y smallint, Val real); Now you want to make a self join based on the X & Y Fields. Hence you should use a sql Join like this: Select ST_SetSRID(ST_MakePoint(a.X, a.Y), 4326) as geom, a.Val as UGRD, b.Val as VGrid from ObsData a inner Join ObsData b on ...


5

By default, you don't have PostGIS 2.0 but 1.5 (see http://packages.ubuntu.com/fr/precise/postgresql-9.1-postgis) By doing CREATE EXTENSION postgis; you're trying to initialize from PostgreSQL with the new loading mechanism a PostGIS version (1.5) that do not support it and in this case, you will need to follow the PostGIS 1.5 official doc If you need 2.0, ...


5

Skip the trig, create view mypoints as select id, st_makepoint( st_x(st_endpoint(geom)) + (st_x(st_endpoint(geom))-st_x(st_startpoint(geom)))/2, st_y(st_endpoint(geom)) + (st_y(st_endpoint(geom))-st_y(st_startpoint(geom)))/2 ) as geom from mytable; then select geom from mypoints where id = 1; should work fine, for all values of id


5

ST_Distance is a calculation which must be executed and evaluated on every row. ST_DWithin can use an index, so it's likely to be much faster.


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 ...



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