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.


11

You're not doing anything wrong, you discovered a hole in our support for PostgreSQL 9.3's new materialized view feature. I've patched all the relevant branches, and you can update your definition of geometry_columns yourself (see the change references in this ticket http://trac.osgeo.org/postgis/ticket/2511) Actually, here's something you can just paste ...


8

I'm assuming that your input lat-long are based on the WGS84 datum. Making a point geometry under this assumption is as easy as using the ST_MakePoint() function: The problem with ST_MakePoint() is that the geometry that is created does not not have a spatial reference. We can set the spatial reference by using the ST_SetSRID() function. So the simplest ...


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


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

The transformation failed for your case since the UpdateGeometrySRID command just changes the metadata, but does not transform coordinates. And when you attempt a transform from 4326->4326, no transform is done since the SRIDs are equal. If you have PostGIS 2.x with a table like this: CREATE TABLE my_table ( gid serial primary key, geom ...


7

See this help document for creating multiple connections. This simple code will create multiple connections: EXEC SQL CONNECT TO testdb1 AS con1 USER testuser; EXEC SQL CONNECT TO testdb2 AS con2 USER testuser; EXEC SQL CONNECT TO testdb3 AS con3 USER testuser; You can then choose a connection to use: EXEC SQL AT connection-name SELECT ...


7

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.


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


6

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


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

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

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


5

In ArcGIS you don't need to create a buffer layer as AllenT suggested. You can do it just by using the selection tools without creating any additional layers. Firstly, you will need a layer representing your coastline. Depending on where your area of interest is, you may need to either digitise it or download it. Then, you can use Select by Location to ...


5

If you create an ancillary table with the attributes (fkey,xcell,ycell), populated from the objectid and a gridding of the maximum Y coordinate (or centroid coordinate or upper-center envelope coordinate), then you could use SQL to update the table, with something like: UPDATE poly SET num = vtab.rownum FROM ( SELECT row_number() over (order ...


5

Probably not exactly a GIS question, but since I have some notes about this, I will paste some of it here. When I need to access amazon services through gui, I use xubuntu (since it is more lightweight) and VNC. The following steps do the install, assuming you have xubuntu-desktop already installed. It comes from several online resources patched together ...


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

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

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


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

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:


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


4

There are lots of different methods, depending what you want to do with the data (routing, general GIS, rendering with Mapnik...). You can find quite a lot on the OSM wiki. osm2postgresql (http://wiki.openstreetmap.org/wiki/Osm2postgresql) is my personal fav but you can have a look at the other ones. They will allow you to import an *.osm file into the ...


4

In general, a database solution will be most useful if you have a large number of features or need to filter or combine data. Do you want to display only polygons of a certain kind? Only polygons within 10 miles of a user-generated point? Do you need to know how much area each polygon covers? Or do you want to know how many features in another table share ...



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