Hot answers tagged postgresql
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 ...
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 ...
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 ...
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 ...
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 ...
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.
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 ...
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 ...
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 ...
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
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.
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 ...
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 ...
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'" ...
I've found the problem. It was a self intersecting polygon. I used ST_IsValidReason to find it.
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.
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 ...
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.
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.
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 ...
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) ;
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
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 ...
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 ...
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 ...
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 ...
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.
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';
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 ...
It's necessary to calculate the actual intersection, e.g. http://postgis.net/docs/ST_Intersection.html SELECT ST_Length(ST_Intersection(line,polygon))
Only top voted, non community-wiki answers of a minimum length are eligible