Tag Info

Hot answers tagged

27

You are confusing SQL and WKT (well-known text). WKT is a like a geometry language to describe shapes, but it is not SQL, which is a language to query and manipulate databases. When working with WKT in an SQL query, it must be text, and not mixed-in with the SQL. Your query works if you properly format the WKT (remove the ",") and set an SRID. For this ...


27

There is a single line function which does this for you. Just use the following SQL query: select UpdateGeometrySRID('Schema Name', 'mytable', 'the_geom', newSRID) ; But, if you are like me, you would be interested in the low level, miniature steps. Logically speaking, the above function is equivalent to the following four step process: In the ...


17

Give a look at this post on my blog: http://www.paolocorti.net/2008/06/06/spatial-database-for-postgres-and-arcgis-users-how-to-choose/ Basically you have 2 options: use PostGis with ArcSde (so you need an ArcSde license, and ArcEditor if you need to edit data) use zigGIS: http://www.obtusesoft.com/ (note that is not tested on ArcGis 10.0). You will just ...


15

Since Postgis is a component of Postgres I would recommend this great book (I own it and I found it extremely valuable) on Postgres performance tuning: http://www.packtpub.com/postgresql-90-high-performance/book It starts from the basics (planning the hardware, os, etc) and then grows into explaining all those misterious configuration params that I never ...


15

You should be able to use the row_number() function as a column in your view. This works for Postgres 8.4 or higher. http://www.postgresql.org/docs/current/static/functions-window.html SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY column_to_sort_by ASC) AS ROW_NUMBER, Col1, Col2 FROM table_name ) myview_name This should work in most ...


15

You can quite easily create the template if it is not there automatically. Here is a description for ubuntu: http://obsessivecoder.com/2010/02/01/installing-postgresql-8-4-postgis-1-4-1-and-pgrouting-1-0-3-on-ubuntu-9-10-karmic-koala/ This is the essential part: sudo su postgres createdb template_postgis createlang plpgsql template_postgis psql -d ...


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.


13

PostGIS in Action, which has been released last month is a good book for worth reading. PART 1 - LEARNING POSTGIS 1 - What is a spatial database? Completed download chapter code and data 2 - Geometry Types Completed download chapter code and data 3 - Data Modeling Completed download chapter code and data 4 - Geometry Functions Completed download chapter ...


13

If you don't need third party support and don't forsee the need to query by type keeping them in the same table works just fine. Alternatively you could use an inheritance model as discussed in chapter 3 of PostGIS in Action. http://www.postgis.us/chapter_03 From an architecture perspective PostGIS doesn't really care if in a query multiple different ...


11

Unless you have used the -x switch then the database that osm2pgsql creates won't include any contributor information. The main use for those databases is rendering which doesn't normally need to know who created an object, and including that information makes the database much larger, so it is normally left out. That information is in the planet file ...


11

If you group, you should get only unique points. CREATE TABLE test_points as SELECT ST_Intersection(a.geom, b.geom), Count(Distinct a.gid) FROM roads as a, roads as b WHERE ST_Touches(a.geom, b.geom) AND a.gid != b.gid GROUP BY ST_Intersection(a.geom, b.geom) ;


11

Generally you can make a new geospatial table like this: SELECT * INTO europe.borders FROM wo_borders WHERE admin_lvl2 = 'eu'; -- Define a primary key ALTER TABLE europe.borders ADD PRIMARY KEY (gid); -- Spatially enable it SELECT Populate_Geometry_Columns('europe.borders'::regclass); However, by doing this you are segregating your database ...


11

The most efficient index for the query expressed in your question is the one on gid as it is the only column that appears in a where expression: CREATE INDEX table_gid ON table (gid); You can safely drop the gist index as it will only consume space and slow inserts/updates/deletes down. Long explanation As I said the most effective index in your case ...


10

Check BostonGIS Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features. If that's too basic: Which topics do you need covered? MySQL is not usable for serious GIS work.


10

The service parameter in the QGIS/PostGIS dialog should be empty. And I spent a bit of time killing the user account "postgres" in windows and removing the names of the databases which are created in the windows registry. After that I could repeatedly fail the dialog by having something in the service parameter of the QGIS Postgis connection dialog.


10

First, make sure you have an index on your geography column. It will speed up the spatial searches: CREATE INDEX geo_cities_geog_idx ON geo_cities USING GIST geog; VACUUM ANALYZE geo_cities(geog); Then, you can use ST_DWithin (with conversions from miles to metres) on a self-joined query: SELECT gc.*, ST_Distance(gc.geog, pt.geog)/1609.344 AS ...


10

PostGIS is an extension of the PostgreSQL database, you can't have PostGIS without PostgreSQL. PostGIS is a robust extension, it defines new datatypes, and provides hundreds of functions so that you can make use of your spatial data. When referring to the specific database you can say it is PostGIS enabled, or if your audience knows your talking about ...


10

you can find very detailed information about Jetty and Apache httpd (from http://wiki.eclipse.org/). i think you should decide what you want from a web server more precisely according to the expectations. Apache httpd is a HTTP server written in C, that is often used to front other web services. Jetty is a full functional and optimized HTTP server ...


10

If you have PostGIS driver capability in the rgdal package then its just a question of creating a connection string and using that. Here I'm connecting to my local database gis using default credentials, so my DSN is rather simple. You might need to add a host, username, or password. See gdal docs for info. > require(rgdal) > dsn="PG:dbname='gis'" ...


10

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


9

I was in the same boat as you just a couple of weeks ago and was able to get a shortest path with relative ease by using the tools/information below: As previously mentioned, I use pgAdminIII as my GUI for writing SQL and managing tables. As for getting started, I would definitely start by following along with @underdark's excellent A Beginner’s Guide to ...


9

The solution can be built entirely in PostGIS. Given a point (house location, i've modeled it as a POINT) and a street segment (segment of street closest to this point, modeled as LINESTRING) you ask: How to tell if point is to the left of a street segment A possible solution is to determine the point on the street segment that is closest to the house ...


9

From version 2 Postgis is enabled by using the extension system. To spatially enable a database, log to your database and then: CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; source: http://postgis.net/docs/postgis_installation.html Note: Only SUPERUSERS roles have the ability to create EXTENSIONS


9

Based on the comments above, as I see it, you have a couple of options. The first is to use an existing Open Source GIS like QGIS or GRASS. You can customise both of these by editing the source code and/or writing pulgins (the former option being more radical and the latter more akin to using Arc Objects). Both support Python. Your second option is to ...


9

I think you will have to decide on certain criteria that define equality for you and that are likely to occur in your application. It is very hard to define likeness for all cases that are "visually" similar. For example: Is a clockwise circle the same as a counterclockwise circle in the same location? Is a circle the same as its approximation with straight ...


9

You should verify that you can connect using psql. Try psql -U username -h localhost dbname. It should prompt for a password then connect. Run SELECT postgis_version(); to verify that PostGIS is active in the database. If you can connect but SELECT postgis_version() reports an error, PostGIS isn't installed in the database: ERROR: function ...


9

Try shp2pgsql. The basic syntax is like: shp2pgsql -s SRID SHAPEFILE.shp SCHEMA.TABLE | psql -h HOST -d DATABASE -U USER I always find this cheatsheet from http://www.bostongis.com useful. If you scroll down a little, you will find simple examples on how to load data. Hope it helps.


9

As unicoletti said, the gist index in the geometry column would only work if you use ST_Contains() in the WHERE expression. For instance, if you would like to know all polygons that contain one another, you could use something like this: SELECT a.gid, b.gid FROM table AS a, table as b WHERE a.gid != b.gid and ST_Contains(a.way, b.way) In this case, ...


8

Two Solutions: 1)Create a single sequence and make all the tables use that sequence, can be done from the beginning or you may create a ID column and update your tables now. To Create the sequence: CREATE SEQUENCE universal_sequence; Then a table: CREATE TABLE ( colname integer NOT NULL DEFAULT nextval('universal_sequence')); To Update an existing ...


8

You can use CASE to create classes. Something like that: SELECT d.disease_type, ST_ConvexHull(ST_Collect(d.the_geom)) As the_geom FROM ( SELECT diseaseobs.the_geom, (CASE diseaseobs.number WHEN number BETWEEN 0 AND 1.5 THEN 'type one' WHEN number BETWEEN 1.6 AND 3 THEN 'type two' ELSE 'other' END ) as ...



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