2

If we have below two tables, how can we find data for all multistring(vicRoads_Data table) lying inside multipolygon(patient_data table)?

I tried this

lung_cancer=# select * from patients_geom,vicRoads_Data where ST_Within(patients_geom.geom,vicRoads_Data.geom)

but no luck.

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)
  • 1
    In addition to Michal Zimmerman's answer below, think about what result you want to get. You have multipolygons and multistrings. If only one part of the multilinestring is within one of the multipolygons, do you still want to return a match? If so, you should think about either using ST_Intersect, or converting your multigeometries to single part. – amball Oct 26 '16 at 6:32
2

Reading the docs leads you to switching the ST_Within arguments to ST_Within(vicRoads_Data.geom, patients_geom.geom).

| improve this answer | |
  • lung_cancer=# select sla_code06,sla_name06,trucks from patients_geom,"vicRoads_Data" where ST_Intersects(" vicRoads_Data".geom,patients_geom.geom); – Arjun Chaudhary Oct 26 '16 at 6:33
  • but the problem is I want all the sum of all the trucks inside that polygon – Arjun Chaudhary Oct 26 '16 at 6:34
  • lung_cancer=# select sla_code06,sla_name06,trucks from patients_geom,"vicRoads_Data" where ST_Intersects(" vicRoads_Data".geom,patients_geom.geom); – Arjun Chaudhary Oct 26 '16 at 6:34
  • I did this its giving truck but not sum of trucks. which can be done by group by sla_code06 but not working – Arjun Chaudhary Oct 26 '16 at 6:35
  • If you want the sum, be specific about this in your original question (edit it to state this). – amball Oct 26 '16 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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