Hot answers tagged postgresql
14
This is a four stage process:
In the geometry_columns table, update the SRID to the required value.
Drop the contraint on the table, by using the following SQL statement
ALTER TABLE mytable DROP CONSTRAINT enforce_srid_the_geom;
Update the SRID'd of the geometry by using the following SQL statement
UPDATE mytable SET the_geom = ST_SetSRID(the_geom, ...
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
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 ...
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 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 ...
8
I sat in on a PostgreSQL/ArcSDE presentation (link1, link2) at this weeks Esri conference and they talked a little about this. They Esri product engineer said (at least I interpreted what she said) that:
ArcGIS 10.1 only supports PostGIS 1.5
ArcGIS 10.1 service pack 1 will support simple geometry features from PostGIS 2.0, but not advanced postgis ...
8
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, ...
7
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.refractions.net/docs/postgis_installation.html
Note: Only SUPERUSERS roles have the ability to create EXTENSIONS
7
You can use st_intersection
Examples 1:
test=# select st_astext(st_intersection('LINESTRING ( 0 0, 0 2 )'::geometry, 'LINESTRING ( 0 0, 2 0 )'::geometry));
st_astext
------------
POINT(0 0)
(1 fila)
Example 2:
test=# select st_astext(st_intersection('LINESTRING ( 1 0, 0 2 )'::geometry, 'LINESTRING ( 0 0, 2 0 )'::geometry));
st_astext
...
7
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 ...
7
Without a doubt, go for Postgres.
PostGres+PostGIS is an fully featured spatial database, and has a lot of documentation, and you'll easily find help from people on forums and here.
MySQL was late to the spatial field, and lacks many features which are there in Postgre+PostGIS. Even the community using MySQL for spatial purposes is minuscule when compared ...
6
According to Paul Ramsey:
First, for patch version increases (e.g. X.Y.Z -> X.Y.(Z+1)) in
PostgreSQL and PostGIS you do not need to do anything at all other
than install the new software. The data can remain in place and
everything will Just Work.
For minor version increases in PostgreSQL (e.g. X.Y.Z -> X.(Y+1).Z)
you need to dump and ...
6
When creating a spatial index on a table it is important to run "vacuum analyze <table>" after that.
For finding nearest points you can use operator <-> introduced in PostGIS 2.0. It actually gives you the distance between two points.
More info can be found here: http://workshops.opengeo.org/postgis-intro/knn.html
SELECT
id
FROM
nodes
ORDER ...
6
That's how shortest_path (Dijkstra's algorithm) behaves in pgRouting. If there are two edges with same source and target, random one (to be precise: first one, that comes out from database) is used. I don't know any fix for that, but there are some workarounds.
If possible, you should split one of those edges into two. I haven't tested it, but it should fix ...
6
Your PostgreSQL / PostGIS database must meet these version requirements:
http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//015100000075000000
Second, you have to register the tables with the Geodatabase. This page gives you more details on how to get to an existing PostGIS table with ArcGIS:
...
5
Your question contains a number of sub-questions - this answer addresses solving the server side part of spell checking.
A while back I've implemented a spelling corrector for addresses based on Peter Norvig's How to Write a Spelling Corrector.
Instead of using books to build your training corpus, your dictionary will be based on words tokenized out of ...
5
Here's a start (not really tested...)
First two assumptions:
I guess your tracks table is a PostGIS spatial table, with a geom column? (If not you'll have to run SELECT AddGeometryColumn(...) to set it up using the Lon/Lat values)
When you say "incremental distance" I'm assuming you mean accumulated distance?
I made two test tables: tracks for the ...
5
No, the <-> operator is not defined on geography. You can use the geometry operator and a cast to geography on the resultants
ST_Distance(geom::geography, ST_GeogFromText(''))
to get the final distances, but potentially if your objects are over the poles or dateline or in the far north the initial geometry ordering of <-> won't be correct so things ...
5
Another option, without needing the function
update points set country = t1.country from
(
select points.oid, countries.name as country from
countries INNER JOIN points on st_contains(countries.wkb_geometry,points.wkb_geometry)
) t1
where t1.oid = points.oid
I suspect (although I haven't tested) that this will be faster than using a nested ...
5
Bring your data into PostgreSQL with something like mysql_fdw, or other methods.
Once you have this table, add a geography column, and populate the new column:
ALTER TABLE places ADD COLUMN geog geography(Point,4326);
UPDATE places SET geog = ST_MakePoint(longitude, latitude);
Now select the nearest 10 places that are within 100 kms:
SELECT places.*, ...
5
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.
5
The ontology you're looking for goes something like this:
Postgres - (Abbreviation of PostgreSQL) The type of your database (as opposed to MySQL, SQLite, Oracle or whatever).
PostGIS - An Extension to PostgreSQL to enable geospatial data storage and querying capabilities. Once this is installed, you might say that the database is "spatially enabled."
...
5
What you are talking about is not stored procedures but spatial inbuilt functions.
Those functions are very powerful and you can do most of the things that you do in a desktop gis directly in the database. It is often faster and more effective, especially when handling huge data sets.
The source that introduced me to the PostGIS functionality was An ...
5
(The answer is based on my and others' comments above; haven't really tested it)
Store the points as MultiPointZM. The best grid size would probably be dependent on access patterns and you need to do some testing on this. A regular grid with a spatial index should make queries quite fast. If 3d access is important then MultiPointZM could be 3D block ...
5
I have found for windows users using the Enterprise Postgres Installers with
StackBuilder the easiest way to get up and running from a clean install (no previous versions of database etc.)
The PostgreSQL installers include the database server, pgAdmin and StackBuilder.
Notes:
**Internet Connection is required during the install as downloads items based on ...
5
My guess is that ST_MakePoint is fastest, but this is easy enough to benchmark with 100k random points.
create temp table points(geom geometry(Point, 4326));
-- repeat the remainder for each <POINT CONSTRUCTOR METHOD>
truncate points;
insert into points(geom)
select <POINT CONSTRUCTOR METHOD>
from generate_series(1,100000);
And here are some ...
5
You have loaded your data with the wrong SRID. 4269 is lon/lat NAD 83. 4326 is lon/lat WGS 84. They are practically the same projection, and both geographic. Judging from your coordinates, the data is actually in some planar projection, though without knowing extra information (like where you are) I can't even take an informed guess as to which one.
4
Tlemill will accept data from a number of sources too, including SQLite. PostGIS is always a good choice for GIS, but I wouldn't necessarily give up on SQLite because, set up right, it can be faster than PostGIS, it is very lightweight and easier to use.
TileMill can use a regular SQLite db or the spatially enabled version, however whether you want your ...
4
Fist about joins and relates. The former term is to "join" one table to another using some criteria, such as data common to the two tables. The later term is more a GIS term for how one spatial data relates to another, e.g. see ST_Relate and DE-9IM. One might join a table to another using a relate. So for your situation, "how to join non-spatial table to ...
4
You should upgrade to GDAL/OGR 1.9.x where the error reporting for PostgreSQL datasource has been improved :
$ python
Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from osgeo import ogr
>>> ogr.UseExceptions()
>>> ds = ...
Only top voted, non community-wiki answers of a minimum length are eligible
