Tag Info

Hot answers tagged

6

Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to be found, thus: SELECT pts.* FROM pts LEFT JOIN polys ON ST_Contains(polys.geom, pts.geom) WHERE polys.id IS NULL; The un-matched rows in a left join are ...


6

Save your Excel as a comma-delimited file, then: COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ',' CSV HEADER; Another option would be ogr2ogr. Edit: after some more searching, the answers to this question should help.


4

Here's what I do for Microsoft Excel files: Preprocess the source Excel Worksheet to ensure that the headers are on the first row, and all data from row 2 and downwards look normal. This may need to be revised if the next step has difficulties Start Microsoft Access and create a Blank database. Navigate to "External Data", and choose to Import Excel, and ...


4

You have to convert your geometry column to WKT to get the point (or any geometry) as text format. http://postgis.refractions.net/documentation/manual-1.4/ST_AsText.html cursor.execute('SELECT ST_AsText(geometry) from test') Probably something in ppygis is missing, because it seems like it converts the point geometry to the PostGis ST_Geometry format but ...


4

You can do this using a Postgres DO block, essentially an anonymous function that you just run once from a psql prompt. Something along these lines: DO $$declare r record; BEGIN FOR r in SELECT srid as srs, f_table_name as name, f_geometry_column as geom FROM geometry_columns WHERE f_table_schema = 'schema_name' LOOP RAISE NOTICE ...


3

Behrman is special case of cylindrical equal area. There is some information about this as well as a warning about an issue in some old Proj4 versions in this thread http://groups.google.com/forum/#!topic/oracle_ugent/2WUt9HmcrHU. Proj4 string to use is +proj=cea +lon_0=0 +lat_ts=30 +x_0=0 +y_0=0 +datum=WGS84 +ellps=WGS84 +units=m +no_defs


2

You're getting an error because: activations.activationid is in a subquery that's been aliased to zz. Since the subquery doesn't really do anything you can flatten it and get INNER JOIN hat.activations.activationid zz ON (zz.activationid = ... However projectsgeom is on a diffrent nesting level too so the above will only change the syntax error. Either ...


2

It's a good bet that your SRID is 4326, and that you are testing if each geometry is within 500° Latitude and/or Longitude, which is a non-sense distance. Since everything is within 500° of each other, the expected return is 1560 * 1560 = 2433600 rows. Try casting to a geography type, which uses distance units of meters (e.g., see ST_DWithin for geography ...


2

QGIS is unable to use the default values that are cast as bpchar. So, a default value of ex. 'f'::bpchar is passed literally to the attribute form, and then inserted literally into the table. Obviously the test '''f''::bpchar' = ANY ('f'::bpchar, 't'::bpchar) will fail. Defaults in the QGIS will work properly if you ALTER your column defaults to be ...


2

The general workflow to construct your polygon would be something like this: st_makepolygon(st_linefromMultipoint(st_collect(points.geom))) But you have to be aware that : your linestring has to be closed order of vertices does mater a rudimary sql querry to create a polygon from a set of points would be the following: with line as ( with mp as ( ...


2

I'd change my tables to something like this: CREATE TABLE regions ( id SERIAL PRIMARY KEY, name text NOT NULL, geom geometry(Polygon,4326) ) CREATE TABLE stations ( id SERIAL PRIMARY KEY, region_id INTEGER NOT NULL REFERENCES regions (id) ON DELETE CASCADE, name text NOT NULL, point GEOMETRY(Point, 4326) NOT NULL, CONSTRAINT fk_reg FOREIGN ...


2

You have pretty much suggested the answer to your own question already, which is that you don't have any indexes. I would suggest that you convert your lat/lon columns to a geography (point) data type, add a spatial index, and rewrite the query to use ST_DWithin, which uses a spatial index, if available, and works with geometries or geographies. ALTER ...


2

Using PGAdminIII create a database using the template_postgis as a template, this copies all the triggers, functions and tables into a new database. Right click on the databases symbol and select 'new database': Then you can import data into it. It is probably best to not use the pg_admin user but if you're the only one seeing the database that should be ...


2

As far as I know, a table cannot have two geometry types (unless the type is a collection). Instead of modifying your existant table, try creating a new one: The geometry column is like any other column, it has one value for each row of the table. You are trying to create one geometry from many geometries, so what you want is not update the geometry of your ...


1

It's a bit difficult to determine exactly what you are trying to achieve. I've provided an example of how to handle the two scenarios that I think you are most likely trying to achieve. I have tried to keep the example short and simple, so I haven't included any SRID stuff and have just used INTs for coordinates. Scenario 1 is adding a line for each of ...


1

I don't have a database handy to test this, but I believe you need to specify if you want the new data checked or the old data checked. Assuming new, try: IF NEW.A >= x AND NEW.B <= y THEN EDIT: Taking a guess on what you're trying to do, let's see if this code will work for you: CREATE FUNCTION periode() RETURNS trigger AS ' BEGIN IF NEW.A ...


1

You can use the function ST_SRID to get the spatial reference identifier. To get the distance between points you can use the function ST_DISTANCE.


1

If that is the query that is used I guess you are out of luck. Is there no WHERE-clause? I have never played with cursors, but as I understand the query, Arc-whatever fetches 1000 rows a time from the whole data set. No matter how efficient and smart Arc-whatever then is to find out what geometries to use and render. All performance is already lost. ...


1

1) Are the two databases running on similar hardware? 2) Have you tuned the Postgres server? By default Postgres is configured to run using minimal resources. That's good because you won't swamp your server just by setting it up, but it handicaps Postgres when you start to try to do real work. As a start here are two links to the Postgres Wiki to get ...


1

With @mkennedy's suggestion, I looked into the conversion information available within Postgis' spatial_ref_sys. The code is available in their subversion repo at http://svn.osgeo.org/postgis/trunk/spatial_ref_sys.sql The conversion statement was: TOWGS84[0,0,0,0,0,0,0] So the result of the conversion calculation was the same NAD83 values that had be ...


1

It kind of depends on you needs and dynamic nature of your points and buffer. If the buffer is dynamic I would store the point and make a view creating the buffer. If the buffer is static, you could store it as a polygon using ST_BUFFER(). Either way you can always extract the other.


1

Without reading all the details I think: You should use ST_DWithin instead You should cast to geography type, or just use the geography type instead of geoemtry type which makes ST_DWithin work with meters instead of degrees So your query will be something like: SELECT ST_DWithin(ST_GeogFromText('POINT(28.239418677985668 -25.75443387513752)'), ...


1

I was able to create the correct answer, so have posted the correct Query here for future users who want to convert GIS data from two joined PostGres Table's to geoJSON I used information from the following pages to help me solve this: http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql ...


1

I know this is an old answer, but as it came up in my Google results it might do the same for others. Unfortunately, the Nominatim install doesn't provide a clear error message, despite it being available. Thus, the answer could be anything. In my case it turned out the hard disk which hosted the database had run full, so the import aborted. When you're ...


1

R.K.'s answer is heading in the right direction. Unfortunately, the pgShapeLoader.app file is needed (within /Applications/OpenGeo/), otherwise the script with plugins.ini will now execute properly (it has no file to reference). I'm not sure the file is available anywhere other than OpenGeoSuite. Ironically, if you download the OpenGeoSuite and install ...



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