1

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.

feature.set('Rating', 'Like');

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 shp2pgsql & psql to insert my shapefile to PostGIS I get something like:

PostGIS table for my Shapefile

I suspect the original features' properties aren't in my table but just their geographical information. So,

  1. Am I right?
  2. Is there any way to import a Shapefile to PostGIS including its properties? (using shp2pgsql | psql or 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

looks like:

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";

closed as off-topic by Ian Turton Jun 16 '17 at 10:32

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This problem cannot or can no longer be reproduced. Changes to the system or to the asker's circumstances may have rendered the question obsolete, or the question does not include a procedure to enable potential answerers to reproduce the same symptoms. Such questions are off-topic as they are unlikely to help future readers, but editing them to include more details can lead to re-opening." – Ian Turton
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    The attributes should be in your PostGis table. How did your shape2pgsql import looks like? – Matte Jun 15 '17 at 17:54
  • Thanks @Matte, I've edited my question adding information about the sql file generated by shp2pgsql. – Jordi Nebot Jun 16 '17 at 7:52
  • you could try to use QGIS and its DB Manager to import your shapefiles into your database. so you would be sure that all fields are in the shapefile are created in the table – LaughU Jun 16 '17 at 8:12
  • Thanks @LaughU! I might try it while learning to see how the property fields are stored in the DB, but at last I'll need to do it programatically since those shapefiles will be uploaded to the server through a web form. – Jordi Nebot Jun 16 '17 at 8:16
  • Your shp2pgsql command is strange. You use -G which does only deliver the attributes and should not deliver the geometries. What columns do you have in that shapefile? If you do not have a gis around just open the .dbf and have a look what columns are there. It seems the shapefile only has the columns "id" and "length". Should there be more? – Matte Jun 16 '17 at 8:21

Browse other questions tagged or ask your own question.