Tag Info

Hot answers tagged

47

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


37

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


21

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


20

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


19

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


19

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


19

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


16

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


16

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


15

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


15

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


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.


15

My guess is that you coordinate transformations have introduced tiny rounding errors (see an example below). As there is no way to set the tolerance in ST_Equals, this is causing ST_Equals to return false for some geometries that only differ in the nth decimal place, as the geometries have to be identical in every respect -- see the intersection matrix ...


14

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


13

I've found the problem. It was a self intersecting polygon. I used ST_IsValidReason to find it.


13

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


13

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


13

My guess is that ST_MakePoint is fastest, but this is easy enough to benchmark with 100k random points. \timing WITH test AS ( SELECT <POINT CONSTRUCTOR METHOD> FROM generate_series(1,100000) ) SELECT count(*) FROM test; And here are some results with PostGIS 2.1 (trunk) on PostgreSQL 9.1, x64 Debian. I did them a few times to get an ...


12

ST_Distance only calculates the distance between two features "as the crow flies". pgRouting on the other hand calculates the actual distance along a network (e.g. road network). Those are two different things and it depends on your use case whether ST_Distance is sufficient or not.


12

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


11

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.


11

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.


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

You need to reference your table twice, giving it different aliases: SELECT ST_Distance(a.geom, b.geom) FROM points_table a, points_table b WHERE a.id='x' AND b.id='y';


11

All spatial table references are held in the geometry_columns metadata table. So try: select * from geometry_columns and you should get just the spatial tables


11

Using Homebrew you should: brew install gdal --with-postgresql or with older versions of gdal: brew install gdal --with-postgres if you have already installed gdal with brew before but without postgresql support, just brew uninstall gdal


11

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


11

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


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



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