Tag Info

New answers tagged

0

How about this (assume test is your linestring table): First nodify the linestring, e.g. create vertices at each intersection while keeping the original vertices in tact. CREATE TABLE node AS SELECT id, ST_Node(wkb_geometry) as wkb_geometry FROM test; Count all distinct points in the new linestrings. Substracting the initial number of vertices from noded ...


0

How about SELECT EXISTS? According to the docs it should run until the first (if any) row is returned.


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


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


0

If you're using ArcGis 10.1: Download the postgrsql-9.0.5 from here http://www.filehorse.com/download-postgresql-64/10965/ It has a windows installer, unlike most of the other sites I found. This is the 32-bit version which is required by ArcGIS 10.1. Open ArcCatalog: Add Database Connection. Select PostgreSQL from the datbase platform drop down. Instance ...


0

To follow up, what I did was to: Keep the original SQL query (easier to understand/maintiain) Create a temp table Insert values into temp table, using ST_MakePoint($stop_lon, $stop_lat) and geom to handle the lat/long values Use the following SQL to create the geoJSON file SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, ...


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


0

I assume that table has following columns a,b,c,x,y,z update statement is something like this update xxx set a = 11, b,=23 then if clause should be IF new.A >=new.x AND new.B <=new.y THEN new.C = new.z


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


0

I haven't used ArcGIS SDKs, but for troubleshooting these kinds of issues, it's helps to enable logging on your database ALTER DATABASE spatial SET log_statement = 'all'; Then run your code and look at the latest log file which is usually in a folder called pg_log in the folder where your PostgreSQL data cluster is. This will confirm that your server ...


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


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.


0

Try SELECT count(*) FROM network WHERE start_id = 10 to make sure your start vertex exists. Additional note: you're using shortest_path function, so your version of pgRouting seems to be 1.x. I recommend you to upgrade to pgRouting 2.0.


0

You need to either restrict the zoom to a workable area or alternatively restrict the amount of painted features. There's a Zoom restrictions for non-base layers. behavior which can be enabled in the map configuration. Maybe it's okay to only display features when the user zoomed close. Alternatively, use the Cluster Features behavior to reduce the amount ...


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


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


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


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


0

I really think that joining on 1 = 1 is not a good idea... Try making your select statement smaller by using ST_DWithin(geom1, geom2, 300) , which is definitely faster than ST_Distance_Sphere() Build indexes! Can you share the data? A portion at least. UPDATE: I guess it would be useful to narrow down the join with distance condition before the join ...


0

Hope it helps someone: The point was that tinyows (version 1.0.0) when adding a new feature when getting a "insert" transaction request, adds a random string as GID. Now GID is either an integer or a biginteger, and defaults to NEXTVAL(sequence) in the Postgres/Postgis table. The GID that tinyows created was too big (out of range). Tinyows works fine ...


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


0

a) You are using the build-in postgresql's geometry data type and not Postgis Point data type. I would follow the production specifications. There are some VERY good reasons to switch the production db to use postgis datatypes but in production enviroments that is not always fearsable. b) you can cast from point to geometry/geography and vice versa select ...


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


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


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


0

When you are running an update, there is no need for a subquery with a SELECT. This is the cause of the more than one row returned by subquery used as an expression error, as the SELECT will return multiple rows. Instead, you want: UPDATE table_name SET shape_area = ST_Area(the_geom::geography)*0.00024711; which will update your table on a row by ...


0

Look for "Topology Checker" plugin and "Processing LWGEOM Provider" plugin that add postgis MakeValid command to Processing Toolbox. there are other gis.stackexchange messages related with these two plugins. regards,


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


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


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


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


0

Im not sure if you have to a FDOtoolbox solution or if that is just your preference, because another good option for this sort of work is ogr2ogr. Once it is installed you can just run something like this from the command line. ogr2ogr -overwrite -f "PostgreSQL" PG:"host=myhost user=myuser dbname=mydb password=mypass" my_file.shp I hope that helps, Rowan


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


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


0

You can create a database view which joins the spatial and non-spatial tables. You need to add a unique id column to the view definition to enable QGIS to load it.


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


0

GROUP BY for geometry uses bounding box. I'm not absolutely sure about geography but I suspect so too. The bounding box is generally a little larger than the object. That said, I wouldn't be using geography or geometry as my primary grouping column. You are almost guaranteed to get the wrong answer given a large enough sampling of data. I am a bit ...


0

QGIS does more work to load a view than it does to load a table. "Viewing" a view in any database viewer like pgAdmin is also slow - it's not a QGIS thing, it's a view thing. My data doesn't change much - about once a year - so I only use tables. I just don't find I need the abstraction that a view provides. (Good discussion here ...


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


3

According to the documentation (chapter 7.3), there are several geometry accessors which might meet your criteria, depending on whether the geometry is a collection, a polygon, or a multi-polygon: ST_NumGeometries - If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, otherwise return NULL. ST_NumInteriorRings - Return the ...


3

Use the geometry accessor ST_NumGeometries. For example SELECT ID, ST_NumGeometries(shape) FROM myTable If your geometries contain geometry types other than polygons, you will need to break them up and keep only the polygons before counting. But from your question this would not be required.



Top 50 recent answers are included