KeyColumn for Creating Query View

[iamMHZ]

I found that your publish_featurestore_sqlview, does not have the primary key ('<keyColumn>primary_key</keyColumn>') parameter. Is there a specific reason behind that?? Are you willing that the primary key(s) be recognized by GeoServer automatically? Also, I want to mention that a complex query can have a primary key made by combining more than one attribute. Is there a solution for handling that?

[iamMHZ]

@iamtekson I would be happy to contiribute on this issue.

[iamtekson]

Please contribute if you like. Thank you @iamMHZ

[trodaway]

I see this feature was removed in an earlier commit, and given the name it seems to have been a diliberate removal. What's the reason behind this / is there a way of reimplementing this feature? Without it I get errors later in my data pipeline, meaning I can't use this package to automate my work fully. If it's remaining removed, I would also suggest updating the documentation which implies the argument still exists. Thanks

[iamtekson]

Hi @trodaway, thank you for raising this issue. I forgot exactly why we removed that feature, but I am going to add it again and try to see how it impact, rest of the things. If you want to send a PR, I am also happy to accept it.

[iamtekson]

Just released the new version v2.5.2 solving this issue. I hope it should work now.

[trodaway]

Thanks for taking a look at this so quickly. I think the keyColumn tag is in the wrong bit of the XML though - whilst GeoServer isn't throwing an error when you POST, if I try to GET the layer via WFS I still get an error regarding the key field. From some testing, it appears (from trial & error, as I don't believe GeoServer's REST API is properly documented) the keyColumn tag should be within the virtualTable tag, opposed to just within the featureType. I'll try to compile a PR to fix this.

[trodaway]

Seems write permission is limited on this repo so can't push / do a PR. This is the code I've got:

def publish_featurestore_sqlview(
        self,
        name: str,
        store_name: str,
        sql: str,
        key_column: Optional[str] = None,
        geom_name: str = "geom",
        geom_type: str = "Geometry",
        srid: Optional[int] = 4326,
        workspace: Optional[str] = None,
    ):
        """

        Parameters
        ----------
        name : str
        store_name : str
        sql : str
        key_column : str, optional
        geom_name : str, optional
        geom_type : str, optional
        workspace : str, optional

        """
        if workspace is None:
            workspace = "default"

        # issue #87
        if key_column is not None:
            key_column_xml = """
            <keyColumn>{}</keyColumn>""".format(key_column)
        else:
            key_column_xml = """"""

        layer_xml = """<featureType>
        <name>{0}</name>
        <enabled>true</enabled>
        <namespace>
            <name>{4}</name>
        </namespace>
        <title>{0}</title>
        <srs>EPSG:{5}</srs>
        <metadata>
            <entry key="JDBC_VIRTUAL_TABLE">
                <virtualTable>
                    <name>{0}</name>
                    <sql>{1}</sql>
                    <escapeSql>true</escapeSql>
                    <geometry>
                        <name>{2}</name>
                        <type>{3}</type>
                        <srid>{5}</srid>
                    </geometry>{6}
                </virtualTable>
            </entry>
        </metadata>
        </featureType>""".format(
            name, sql, geom_name, geom_type, workspace, srid, key_column_xml
        )

        # rest API url
        url = "{}/rest/workspaces/{}/datastores/{}/featuretypes".format(
            self.service_url, workspace, store_name
        )

        # headers
        headers = {"content-type": "text/xml"}

        # request
        r = requests.post(
            url,
            data=layer_xml,
            auth=(self.username, self.password),
            headers=headers,
        )

        if r.status_code == 201:
            return r.status_code
        else:
            raise GeoserverException(r.status_code, r.content)

[iamtekson]

Yes you are write. I will make another PR to solve this issue. Thanks again!

[trodaway]

Thanks for sorting! I assume 2.5.3 may take a couple of hours to get onto conda-forge, but should be good by tomorrow? I didn't see how fast it was yesterday.

[iamtekson]

I already released the new version!

[trodaway]

Thanks - it just didn't come through onto conda-forge for another couple of hours.

[iamtekson]

Yes, it takes few hours to create the build file for Conda.