Re: [postgis-users] view raster data and query performance
Il 27/07/2012 23:22, Melpati, Muni ha scritto: I have also changed postgresql.config file to improve the performance with no noticeble difference. Can anyone faced this problem or have any suggestion to improve the performance? Thanks in advance. Agreed, it is so slow to be barely usable in real world. I suppose this is due to the GDAL provider, right? All the best. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] distance from centroid to the points that form the outer boundary of the polygon
Hi All, I am looking for suggestions for the following problem I have at hand. 1. I have a table that contain multipolygons 2. I want to calculate the maximum distance of points that form the polygon from the centroid of the polygon. For example of the polygon is formed of 50 points then I want to have distance of all the 50 points from the centroid of the polygon. I am not sure if someone has done this before. But I am finding it difficult to implement it. Any help in this regards is welcome… Best,YJS ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] distance from centroid to the points that form the outer boundary of the polygon
Hi there, How about calculating the MinimumBoundingCircle of your geometry first http://postgis.refractions.net/docs/ST_MinimumBoundingCircle.html. Once you have the circle, it shouldn't be too difficult to derive the radius from this? HTH Edward From: yaminijsi...@live.com To: postgis-users@postgis.refractions.net Date: Mon, 30 Jul 2012 15:04:15 +0200 Subject: [postgis-users] distance from centroid to the points that form the outer boundary of the polygon Hi All, I am looking for suggestions for the following problem I have at hand. 1. I have a table that contain multipolygons 2. I want to calculate the maximum distance of points that form the polygon from the centroid of the polygon. For example of the polygon is formed of 50 points then I want to have distance of all the 50 points from the centroid of the polygon. I am not sure if someone has done this before. But I am finding it difficult to implement it. Any help in this regards is welcome… Best,YJS ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Help with PL/PGSQL!
So, I've got this PL/PGSQL script that doesn't want to work. I've got the
TIGER geocoder up and running and I can get whatever I want from that.
However, when I try and combine ST_Within with the output from GEOCODE I
get an error. I assume there's a simple syntax error here, but I can't
figure it out. I've been able to get this to work when just cutting and
pasting the actual geometry data into where geocoded is below, but
replacing it with the variable name gives me an error (sldu is a table with
Senate districts and sldust is the district number column):
CREATE OR REPLACE FUNCTION get_district(address text)
RETURNS text AS
$$
DECLARE
district RECORD;
geocoded RECORD;
BEGIN
SELECT geomout into geocoded from geocode(address) as g;
SELECT sldust from sldu into district where ST_Within(geocoded,
the_geom);
return district;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
geocoder=# select get_district('1700 C St Lincoln, NE');
ERROR: function st_within(record, geometry) does not exist
LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT sldust from sldu where ST_Within( $1 , the_geom)
CONTEXT: PL/pgSQL function get_district line 6 at SQL statement
--
Eric Aspengren
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help with PL/PGSQL!
If you're sure you're in a database based on the PostGIS template, I'd
try putting the schema name in front of the function name, i.e.
public.ST_Within(..., see if that's the issue.
-Greg Kramida
On 7/30/2012 2:32 PM, Eric Aspengren wrote:
So, I've got this PL/PGSQL script that doesn't want to work. I've got
the TIGER geocoder up and running and I can get whatever I want from
that. However, when I try and combine ST_Within with the output from
GEOCODE I get an error. I assume there's a simple syntax error here,
but I can't figure it out. I've been able to get this to work when
just cutting and pasting the actual geometry data into where
geocoded is below, but replacing it with the variable name gives me
an error (sldu is a table with Senate districts and sldust is the
district number column):
CREATE OR REPLACE FUNCTION get_district(address text)
RETURNS text AS
$$
DECLARE
district RECORD;
geocoded RECORD;
BEGIN
SELECT geomout into geocoded from geocode(address) as g;
SELECT sldust from sldu into district where ST_Within(geocoded,
the_geom);
return district;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
geocoder=# select get_district('1700 C St Lincoln, NE');
ERROR: function st_within(record, geometry) does not exist
LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT sldust from sldu where ST_Within( $1 , the_geom)
CONTEXT: PL/pgSQL function get_district line 6 at SQL statement
--
Eric Aspengren
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] distance from centroid to the points that form the outer boundary of the polygon
Hi Yamini, Are you sure that multipolygons is what you want to work with? Since a multipolygon can consist of multiple polygons (obviously) who share a common centroid that can be way outside your actual polygons. That said, I would make use of ST_Dumppoints to do that. Something like: WITH pointsdump AS ( SELECT gid, ST_Centroid(geom) AS centre, (ST_Dumppoints(geom)).geom AS point FROM polygontable ) SELECT gid, ST_Distance(centre, point) AS distance FROM pointsdump === Now for every point in a geometry with a known gid you will see the distance to it's centroid. You can do statistics on these numbers with something like: SELECT gid, avg(distance) avg, max(distance) max, etc... FROM results GROUP BY gid Cheers, Tom On 30-7-2012 15:04, Yamini Singh wrote: Hi All, I am looking for suggestions for the following problem I have at hand. 1.I have a table that contain multipolygons 2.I want to calculate the maximum distance of points that form the polygon from the centroid of the polygon. For example of the polygon is formed of 50 points then I want to have distance of all the 50 points from the centroid of the polygon. I am not sure if someone has done this before. But I am finding it difficult to implement it. Any help in this regards is welcome... Best, YJS ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help with PL/PGSQL!
You have geocoded declared as a type record. ST_Within needs that to be of
type geometry.
On Mon, Jul 30, 2012 at 1:32 PM, Eric Aspengren ericas...@gmail.com wrote:
So, I've got this PL/PGSQL script that doesn't want to work. I've got the
TIGER geocoder up and running and I can get whatever I want from that.
However, when I try and combine ST_Within with the output from GEOCODE I
get an error. I assume there's a simple syntax error here, but I can't
figure it out. I've been able to get this to work when just cutting and
pasting the actual geometry data into where geocoded is below, but
replacing it with the variable name gives me an error (sldu is a table with
Senate districts and sldust is the district number column):
CREATE OR REPLACE FUNCTION get_district(address text)
RETURNS text AS
$$
DECLARE
district RECORD;
geocoded RECORD;
BEGIN
SELECT geomout into geocoded from geocode(address) as g;
SELECT sldust from sldu into district where ST_Within(geocoded,
the_geom);
return district;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
geocoder=# select get_district('1700 C St Lincoln, NE');
ERROR: function st_within(record, geometry) does not exist
LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT sldust from sldu where ST_Within( $1 , the_geom)
CONTEXT: PL/pgSQL function get_district line 6 at SQL statement
--
Eric Aspengren
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
David William Bitner
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help with PL/PGSQL!
Well, that was simple enough! Thanks! (as Eric rolls his eyes at himself)
On Mon, Jul 30, 2012 at 3:13 PM, David William Bitner bit...@gyttja.orgwrote:
You have geocoded declared as a type record. ST_Within needs that to be of
type geometry.
On Mon, Jul 30, 2012 at 1:32 PM, Eric Aspengren ericas...@gmail.comwrote:
So, I've got this PL/PGSQL script that doesn't want to work. I've got the
TIGER geocoder up and running and I can get whatever I want from that.
However, when I try and combine ST_Within with the output from GEOCODE I
get an error. I assume there's a simple syntax error here, but I can't
figure it out. I've been able to get this to work when just cutting and
pasting the actual geometry data into where geocoded is below, but
replacing it with the variable name gives me an error (sldu is a table with
Senate districts and sldust is the district number column):
CREATE OR REPLACE FUNCTION get_district(address text)
RETURNS text AS
$$
DECLARE
district RECORD;
geocoded RECORD;
BEGIN
SELECT geomout into geocoded from geocode(address) as g;
SELECT sldust from sldu into district where ST_Within(geocoded,
the_geom);
return district;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
geocoder=# select get_district('1700 C St Lincoln, NE');
ERROR: function st_within(record, geometry) does not exist
LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT sldust from sldu where ST_Within( $1 , the_geom)
CONTEXT: PL/pgSQL function get_district line 6 at SQL statement
--
Eric Aspengren
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
David William Bitner
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Eric Aspengren
(402) 478-VOTE
ericas...@gmail.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] view raster data and query performance
I cannot answer for that. If anyone had this problem and fixed it, I would like to know how they have done it. Moreover, When I tries to store raster data outside the database, I could not even add the layer from QGIS. When I tried to add the layer using postgis plugin, I find raster layer related columns (pixel type, is extrernal file, pixel size x, pixel size y in the load potgis raster layer interface, has values none. I must have done something wrong. When I add this raster layer, QGIS crashes. Here is the syntax I have used: raster2pgsql -I -R -e -Y -F -s 26986 -t 128x128 c:\PostGIS\raster\dowell.jpg public.raster | psql -U postgres -d gisdb -h localhost -p 5432 Anything wrong with it. Thanks. Date: Mon, 30 Jul 2012 12:16:52 +0200 From: Paolo Cavallini cavall...@faunalia.itmailto:cavall...@faunalia.it Subject: Re: [postgis-users] view raster data and query performance To: postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Message-ID: 50165f14.6060...@faunalia.itmailto:50165f14.6060...@faunalia.it Content-Type: text/plain; charset=iso-8859-1 Il 27/07/2012 23:22, Melpati, Muni ha scritto: I have also changed postgresql.config file to improve the performance with no noticeble difference. Can anyone faced this problem or have any suggestion to improve the performance? Thanks in advance. Agreed, it is so slow to be barely usable in real world. I suppose this is due to the GDAL provider, right? All the best. -- Paolo Cavallini - Faunalia ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] view raster data and query performance
For QGIS, you need to use the PostGIS Raster plugin, not the PostGIS plugin. -bborie On 07/30/2012 01:26 PM, Melpati, Muni wrote: I cannot answer for that. If anyone had this problem and fixed it, I would like to know how they have done it. Moreover, When I tries to store raster data outside the database, I could not even add the layer from QGIS. When I tried to add the layer using postgis plugin, I find raster layer related columns (pixel type, is extrernal file, pixel size x, pixel size y in the load potgis raster layer interface, has values none. I must have done something wrong. When I add this raster layer, QGIS crashes. Here is the syntax I have used: raster2pgsql -I -R -e -Y -F -s 26986 -t 128x128 c:\PostGIS\raster\dowell.jpg public.raster | psql -U postgres -d gisdb -h localhost -p 5432 Anything wrong with it. Thanks. Date: Mon, 30 Jul 2012 12:16:52 +0200 From: Paolo Cavallini cavall...@faunalia.itmailto:cavall...@faunalia.it Subject: Re: [postgis-users] view raster data and query performance To: postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Message-ID: 50165f14.6060...@faunalia.itmailto:50165f14.6060...@faunalia.it Content-Type: text/plain; charset=iso-8859-1 Il 27/07/2012 23:22, Melpati, Muni ha scritto: I have also changed postgresql.config file to improve the performance with no noticeble difference. Can anyone faced this problem or have any suggestion to improve the performance? Thanks in advance. Agreed, it is so slow to be barely usable in real world. I suppose this is due to the GDAL provider, right? All the best. -- Paolo Cavallini - Faunalia ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] view raster data and query performance
Il 30/07/2012 23:04, Bborie Park ha scritto: For QGIS, you need to use the PostGIS Raster plugin, not the PostGIS plugin. not quite right: take QGIS 1.8 (latest released) and use the embedded DB Manager, dragging and dropping on the canvas should just work. All the best. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
