Context: I'm building a WebApp with OpenLayers where users interact with some vector data (MultiLineStrings from Shapefiles) loaded from a GeoServer using WFS.
Basically they rate (like/dislike) some Features and choose the reasons why they like or dislike from a list. This information is easily stored in the Features using OpenLayers.
For every user session I store all rated features as a GeoJSON into a PostGIS table. It works.
Problem: I'd like to also update a copy of the original Shapefile as a PostGIS table where each feature would contain a summary of all ratings. Let's say 87 users liked the feature with
id = 42. I'd like my
feat.42 to have a LIKES property with value 87. And I'd like to directly update those LIKES in the DATABASE. Something like:
UPDATE schema.shapefile_table SET Likes = newValue WHERE id = 42
But, when I use
psql to insert my shapefile to PostGIS I get something like:
I suspect the original features' properties aren't in my table but just their geographical information. So,
- Am I right?
- Is there any way to import a Shapefile to PostGIS including its properties? (using
shp2pgsql | psqlor something as well non-interactive)
In any case, is there a better scenario to solve what I'm trying to do?
Edit: My SQL file generated by:
shp2pgsql -G -I myshapefile.shp
SET CLIENT_ENCODING TO UTF8; SET STANDARD_CONFORMING_STRINGS TO ON; BEGIN; CREATE TABLE "myshapefile" (gid serial, "id" int4, "length" float8, "geog" geography(MULTILINESTRING,4326)); ALTER TABLE "myshapefile" ADD PRIMARY KEY (gid); INSERT INTO "myshapefile" ("id","length",geog) VALUES ('0','184.68','0105000020E61000000100000001020000000C000000944DB982ED76184155BE671D0C8F514152499DE0CC76184172DEFFF60B8F5141DA5889C9B8761841EE0912E40B8F5141BDFDB9A8987618419641B5C60B8F51417638BA4A75761841AA622AA90B8F51414FB0FF6A4D761841245F098E0B8F5141A48B4D3B05761841C47B0E620B8F51412A8E03AFB175184149BDA7330B8F514182C64C525175184154AA44FD0A8F5141E083D77EED74184155DD23CB0A8F5141041C4215547418410DE02D840A8F5141A702EE690B741841F644D74C0A8F5141'); INSERT INTO "myshapefile" ("id","length",geog) VALUES ('1','83.37','0105000020E610000001000000010200000006000000D2FD9CB278721841BA8443AC3A8F51415C1E6B669D72184177853ED53A8F51417C28D142F57218410CCB9F023B8F5141D670914B4173184146ED7E233B8F5141651BB86381731841268FA7433B8F5141931804E6C573184174081C773B8F5141'); ... CREATE INDEX ON "myshapefile" USING GIST ("geog"); COMMIT; ANALYZE "myshapefile";