2

I was able to find all the lines lying inside each polygon using below query. But, now I want what percentage of total length of that road or multilinestring lies inside multipolygon.

Query

select sla_code06,sla_name06,sum(trucks) as total_trucks,sum(
lung_deaths+heart_deaths+respiratory_deaths+copd_deaths) as total_deaths from patients_geom,"vicRoads_Data
" where ST_Intersects("vicRoads_Data".geom,patients_geom.geom) group by sla_code06,sla_name06;

Tables
lung_cancer=# \d patients_geom;
                      Table "public.patients_geom"
               Column       |          Type          | Modifiers
        --------------------+------------------------+-----------
         lung_deaths        | real                   |
         heart_deaths       | real                   |
         respiratory_deaths | real                   |
         copd_deaths        | real                   |
         area_code          | integer                |
         area_name          | character(250)         |
         gid                | integer                |
         state_code         | character varying(1)   |
         sla_code06         | character varying(9)   |
         sla_name06         | character varying(50)  |
         sla_5digit         | character varying(5)   |
         geom               | geometry(MultiPolygon) |


        lung_cancer=# \d "vicRoads_Data";
                                              Table "public.vicRoads_Data"
           Column   |           Type            |                           Modifiers
        ------------+---------------------------+---------------------------------------------------------------
         gid        | integer                   | not null default nextval('"vicRoads_Data_gid_seq"'::regclass)
         allveh_amp | character varying(254)    |
         label      | character varying(254)    |
         allveh_pmp | character varying(254)    |
         road       | character varying(254)    |
         trucks     | integer                   |
         allvehs    | integer                   |
         __gid      | integer                   |
         directio_1 | character varying(254)    |
         hf_1       | integer                   |
         year       | integer                   |
         per_trucks | character varying(254)    |
         geom       | geometry(MultiLineString) |
        Indexes:
            "vicRoads_Data_pkey" PRIMARY KEY, btree (gid)
            "vicRoads_Data_geom_idx" gist (geom)
  • This is more of a "How to use SQL" than a "How to use GIS" question. Obtaining the portion inside is just use of ST_Intersection(), and percent is just a fraction. You haven't provided the versions of PostgreSQL or PostGIS, or the SRID of the table's geometry column, any of which could impact methodolgy. – Vince Oct 26 '16 at 12:59
4

ST_Length and ST_Intersection should do what you are searching for:

SELECT sla_code06, 
  sla_name06, SUM(trucks) AS total_trucks,
  SUM(lung_deaths+heart_deaths+respiratory_deaths+copd_deaths) AS total_deaths, 
  SUM(ST_Length(ST_Intersection(vicRoads_Data.geom, patients_geom.geom))) AS geom_length, 
  (ST_Length(ST_Intersection(vicRoads_Data.geom, patients_geom.geom)) 
        / ST_Length(vicRoads_Data.geom) * 100) AS geom_length_percentage

FROM patients_geom, vicRoads_Data 
WHERE ST_Intersects(vicRoads_Data.geom, patients_geom.geom) 
GROUP BY sla_code06, sla_name06;
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.