I have 3 files. 1 shapefile and 2 DBF files. For those of who are not a familiar with shapefiles (GIS data format), they are essentially database tables with rows and columns that have spatial geometry such as point, line, polygon etc., which can be displayed visually and used in calculations in a GIS software.
trucknetwork (shapefile) - 564 rows (column
sri links to
table1) - specific truck road network in NJ with no road names:
gid integer NOT NULL DEFAULT nextval('trucknetwork_gid_seq'::regclass), id numeric(10,0), sri character varying(20), mp_start double precision, mp_end double precision, descriptio character varying(50), road_type character varying(50), geom geometry(MultiLineStringM), CONSTRAINT trucknetwork_pkey PRIMARY KEY (gid)
table1 - 488715 rows (links to shapefile with
sri column and
seg_guid column) - start and end points of all NJ roads:
gid integer NOT NULL DEFAULT nextval('table1_gid_seq'::regclass), objectid integer, seg_guid character varying(38), sri character varying(20), route_type numeric, sld_mp_st numeric, sld_mp_end numeric, CONSTRAINT table1_pkey PRIMARY KEY (gid)
table2 - 669557 rows (links to
seg_guid column) - all NJ road names:
gid integer NOT NULL DEFAULT nextval('table2_gid_seq'::regclass), objectid integer, seg_guid character varying(38), s_name character varying(254), CONSTRAINT table2_pkey PRIMARY KEY (gid)
I want to join these 3 tables into 1 table - with the result being 564 rows of each road segment with its correct name. It is a one-to-many relationship.
I have posted this question on GIS stackexchange but I did not get a lot of feedback and I want to do this task in SQL not a GIS software.
I have tried left join, inner join - and I have tried the group by but for every query I keep getting over 100,000 + records
select shp.descriptio as descr, shp.road_type as road_type, shp.geom as geom, t2.s_name as street_name into networkEX from trucknetwork as shp join table1 as t1 on shp.sri = t1.sri join table2 as t2 on t1.seg_guid = t2.seg_guid;
I am not sure what the logic should be. Subquery? Do I have to break this up into smaller steps?