communication:ctt_2010:cenlr:temp

Tue, 23 Apr 2019 09:44:00 +0200

Requête SQL

DROP VIEW md.description_lot;
CREATE OR REPLACE VIEW md.description_lot AS 
 SELECT lot_donnee.id_lot, 
        CASE
            WHEN lot_donnee.libelle IS NULL THEN lot_donnee.code
            ELSE lot_donnee.libelle
        END AS lot_donnee_libelle, lot_donnee.id_etude AS lot_donnee_id_etude, lot_donnee.type_donnee AS lot_donnee_type_donnee, lot_donnee.resume AS lot_donnee_resume, lot_donnee.description_genealogie AS lot_donnee_description_genealogie, lot_donnee.echelle_utilisation AS lot_donnee_echelle_utilisation, lot_donnee.licence AS lot_donnee_licence, lot_donnee.limitation_acces_public AS lot_donnee_limitation_acces_public, lot_donnee.qualite_thematique AS lot_donnee_qualite_thematique, lot_donnee.id_protocole AS lot_donnee_id_protocole, etude.id_etude AS etude_id_etude, etude.nom_etude AS etude_nom_etude, etude.cahier_des_charges AS etude_cahier_des_charges, etude.date_debut AS etude_date_debut, etude.date_fin AS etude_date_fin, etude.description AS etude_description, etude.lien_rapport_final AS etude_lien_rapport_final, protocole.id_protocole AS protocole_id_protocole, protocole.libelle AS protocole_libelle, protocole.auteurs AS protocole_auteurs, protocole.id_biblio AS protocole_id_biblio, lower(spatial_ref_sys.auth_name)||' '||spatial_ref_sys.auth_srid AS srid, 
        CASE WHEN lot_donnee.type_donnee::text LIKE '%faune%' OR lot_donnee.type_donnee::text LIKE '%flore%' THEN 'Species distribution' ELSE 'Habitats and biotopes' END AS annexe
   FROM md.lot_donnee
   JOIN md.etude USING (id_etude)
   JOIN md.protocole USING (id_protocole),
   geometry_columns, spatial_ref_sys
   WHERE f_table_name = type_donnee::text AND geometry_columns.srid = spatial_ref_sys.srid
  ORDER BY lot_donnee.id_lot;

Script php

<?php
header ("Content-Type:text/xml");
echo('<?xml version="1.0" encoding="UTF-8"?>');
if(!$conn=pg_connect('dbname=sicen user=titi password=toto host=192.168.1.230')){echo("pas de connexion à la bdd");}
 
$query1='select * from md.description_lot WHERE id_lot ='.$_GET['id_lot'].' LIMIT 1;';
//echo $query1;
$result = pg_query($conn, $query1);
if (!$result) {
  echo "An error occured.\n";
  exit;
}
$assoc = pg_fetch_assoc($result);
echo('<!--');
print_r($assoc);
echo('-->');
/*
select * from md.description_lot WHERE id_lot =14 LIMIT 1;Array
(
    [id_lot] => 14
    --[lot_donnee_libelle] => Titre de mon lot de données
    [lot_donnee_code] => etude_001_protocole_002_lignes_faune
    [lot_donnee_id_etude] => 1
    [lot_donnee_type_donnee] => ligne_faune
    [lot_donnee_resume] => 
    [lot_donnee_description_genealogie] => 
    [lot_donnee_echelle_utilisation] => 
    [lot_donnee_licence] => 
    [lot_donnee_limitation_acces_public] => 
    [lot_donnee_qualite_thematique] => 
    [lot_donnee_id_protocole] => 2
    [etude_id_etude] => 1
    [etude_nom_etude] => Test de l'application
    [etude_cahier_des_charges] => aucun
    [etude_date_debut] => 
    [etude_date_fin] => 
    [etude_description] => 
    [etude_lien_rapport_final] => 
    [protocole_id_protocole] => 2
    [protocole_libelle] => IPA
    [protocole_auteurs] => 
    [protocole_id_biblio] => 
 
)
*/
$xml_string = '<gmd:MD_Metadata xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:cat="http://www.opengis.net/cat/csw" xmlns:csw="http://www.opengis.net/cat/csw/2.0.2" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dc2="http://www.purl.org/dc/elements/1.1/" xmlns:dct="http://purl.org/dc/terms/" xmlns:dct2="http://www.purl.org/dc/terms/" xmlns:fra="http://www.cnig.gouv.fr/2005/fra" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:gfc="http://www.isotc211.org/2005/gfc" xmlns:gmi="http://www.isotc211.org/2005/gmi" xmlns:gml="http://www.opengis.net/gml" xmlns:gmx="http://www.isotc211.org/2005/gmx" xmlns:ins="http://www.inspire.org" xmlns:ns17="http://www.opengis.net/ows/1.1" xmlns:ns9="http://www.mdweb-project.org/files/xsd" xmlns:ogc="http://www.opengis.net/ogc" xmlns:ows="http://www.opengis.net/ows" xmlns:srv="http://www.isotc211.org/2005/srv" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.isotc211.org/2005/gmd http://schemas.opengis.net/iso/19139/20060504/gmd/gmd.xsd">
    <gmd:fileIdentifier>
        <gco:CharacterString>MDweb-40884177-aeec-4b72-9a7f-2f5f80f7afcd</gco:CharacterString>
    </gmd:fileIdentifier>
    <gmd:language>
        <gco:CharacterString>fre</gco:CharacterString>
    </gmd:language>
    <gmd:hierarchyLevel>
        <gmd:MD_ScopeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#dataset" codeListValue="dataset"/>
    </gmd:hierarchyLevel>
    <gmd:contact>
        <gmd:CI_ResponsibleParty>
            <gmd:organisationName>
                <gco:CharacterString>Conservatoire des Espaces Naturels du Languedoc-Roussillon</gco:CharacterString>
            </gmd:organisationName>
            <gmd:contactInfo>
                <gmd:CI_Contact>
                    <gmd:address>
                        <gmd:CI_Address>
                            <gmd:electronicMailAddress>
                                <gco:CharacterString>cenlr@cenlr.org</gco:CharacterString>
                            </gmd:electronicMailAddress>
                        </gmd:CI_Address>
                    </gmd:address>
                    </gmd:CI_Contact>
            </gmd:contactInfo>
            <gmd:role>
                <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
            </gmd:role>
        </gmd:CI_ResponsibleParty>
    </gmd:contact>
    <gmd:dateStamp>
        <gco:DateTime>2010-10-28T00:00:00+02:00</gco:DateTime>
    </gmd:dateStamp>
    <gmd:metadataStandardName>
        <gco:CharacterString>x-urn:mdweb:schema:exp:ISO19115:INSPIRE:nature-sdi:spp-distrib:mdweb-vector</gco:CharacterString>
    </gmd:metadataStandardName>
    <gmd:metadataStandardVersion>
        <gco:CharacterString>2010.04</gco:CharacterString>
    </gmd:metadataStandardVersion>
    <gmd:spatialRepresentationInfo>
        <gmd:MD_VectorSpatialRepresentation>
            <gmd:geometricObjects>
                <gmd:MD_GeometricObjects>
                    <gmd:geometricObjectType>
                        <gmd:MD_GeometricObjectTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#point" codeListValue="point"/>
                    </gmd:geometricObjectType>
                </gmd:MD_GeometricObjects>
            </gmd:geometricObjects>
        </gmd:MD_VectorSpatialRepresentation>
    </gmd:spatialRepresentationInfo>
    <gmd:referenceSystemInfo>
        <gmd:MD_ReferenceSystem>
            <gmd:referenceSystemIdentifier>
                <gmd:RS_Identifier>
                    <gmd:code>
		    <gco:CharacterString>'.$assoc['srid'].'</gco:CharacterString>
                    </gmd:code>
                </gmd:RS_Identifier>
            </gmd:referenceSystemIdentifier>
        </gmd:MD_ReferenceSystem>
    </gmd:referenceSystemInfo>
    <gmd:referenceSystemInfo>
        <gmd:MD_ReferenceSystem>
            <gmd:referenceSystemIdentifier>
                <gmd:RS_Identifier>
                    <gmd:code>
                        <gco:CharacterString>Gregorian Calendar</gco:CharacterString>
                    </gmd:code>
                </gmd:RS_Identifier>
            </gmd:referenceSystemIdentifier>
        </gmd:MD_ReferenceSystem>
    </gmd:referenceSystemInfo>
    <gmd:identificationInfo>
        <ns9:NATSDI_DataIdentification>
            <gmd:citation>
                <gmd:CI_Citation>
                    <gmd:title>
                        <gco:CharacterString>'.$assoc['lot_donnee_libelle'].'</gco:CharacterString>
                    </gmd:title>
                    <gmd:date>
                        <gmd:CI_Date>
                            <gmd:date>
                                <gco:DateTime>2010-10-28T00:00:00+02:00</gco:DateTime>
                            </gmd:date>
                            <gmd:dateType>
                                <gmd:CI_DateTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publication" codeListValue="publication"/>
                            </gmd:dateType>
                        </gmd:CI_Date>
                    </gmd:date>
                    <gmd:identifier>
                        <gmd:RS_Identifier>
                            <gmd:code>
                                <gco:CharacterString>http://www.cenlr.org/'.$assoc['id_lot'].'</gco:CharacterString>
                            </gmd:code>
                        </gmd:RS_Identifier>
                    </gmd:identifier>
                </gmd:CI_Citation>
            </gmd:citation>
            <gmd:abstract>
                <gco:CharacterString>'.$assoc['lot_donnee_resume'].'</gco:CharacterString>
            </gmd:abstract>
            <gmd:pointOfContact>
                <gmd:CI_ResponsibleParty>
                    <gmd:organisationName>
                        <gco:CharacterString>Conservatoire des Espaces Naturels du Languedoc-Roussillon</gco:CharacterString>
                    </gmd:organisationName>
                    <gmd:contactInfo>
                        <gmd:CI_Contact>
                            <gmd:address>
                                <gmd:CI_Address>
                                    <gmd:electronicMailAddress>
                                        <gco:CharacterString>cenlr@cenlr.org</gco:CharacterString>
                                    </gmd:electronicMailAddress>
                                </gmd:CI_Address>
                            </gmd:address>
                            </gmd:CI_Contact>
                    </gmd:contactInfo>
                    <gmd:role>
                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
                    </gmd:role>
                </gmd:CI_ResponsibleParty>
            </gmd:pointOfContact>
            <gmd:pointOfContact>
                <gmd:CI_ResponsibleParty>
                    <gmd:organisationName>
                        <gco:CharacterString>responsable de l\'Ã©tude</gco:CharacterString>
                    </gmd:organisationName>
                    <gmd:contactInfo>
                        <gmd:CI_Contact>
                            <gmd:address>
                                <gmd:CI_Address>
                                    <gmd:electronicMailAddress>
                                        <gco:CharacterString>cenlr@cenlr.rog</gco:CharacterString>
                                    </gmd:electronicMailAddress>
                                </gmd:CI_Address>
                            </gmd:address>
                            </gmd:CI_Contact>
                    </gmd:contactInfo>
                    <gmd:role>
                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
                    </gmd:role>
                </gmd:CI_ResponsibleParty>
            </gmd:pointOfContact>
            <gmd:resourceMaintenance>
                <gmd:MD_MaintenanceInformation>
                    <gmd:updateScope>
                        <gmd:MD_ScopeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#dataset" codeListValue="dataset"/>
                    </gmd:updateScope>
                </gmd:MD_MaintenanceInformation>
            </gmd:resourceMaintenance>
            <gmd:descriptiveKeywords>
                <gmd:MD_Keywords>
                    <gmd:keyword>
                        <gco:CharacterString>'.$ssoc['annexe'].'Species distribution</gco:CharacterString>
                    </gmd:keyword>
                    <gmd:thesaurusName>
                        <gmd:CI_Citation>
                            <gmd:title>
                                <gco:CharacterString>GEMET - INSPIRE themes, version 1.0</gco:CharacterString>
                            </gmd:title>
                            <gmd:date>
                                <gmd:CI_Date>
                                    <gmd:date>
                                        <gco:DateTime>2008-06-01T00:00:00+02:00</gco:DateTime>
                                    </gmd:date>
                                    <gmd:dateType>
                                        <gmd:CI_DateTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publication" codeListValue="publication"/>
                                    </gmd:dateType>
                                </gmd:CI_Date>
                            </gmd:date>
                            <gmd:edition>
                                <gco:CharacterString>version 1.0</gco:CharacterString>
                            </gmd:edition>
                            <gmd:citedResponsibleParty>
                                <gmd:CI_ResponsibleParty>
                                    <gmd:organisationName>
                                        <gco:CharacterString>EIONET</gco:CharacterString>
                                    </gmd:organisationName>
                                    <gmd:contactInfo>
                                        <gmd:CI_Contact>
                                            <gmd:onlineResource>
                                                <gmd:CI_OnlineResource>
                                                    <gmd:linkage>
                                                        <gmd:URL>http://www.eionet.europa.eu/gemet/</gmd:URL>
                                                    </gmd:linkage>
                                                </gmd:CI_OnlineResource>
                                            </gmd:onlineResource>
                                        </gmd:CI_Contact>
                                    </gmd:contactInfo>
                                    <gmd:role>
                                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publisher" codeListValue="publisher"/>
                                    </gmd:role>
                                </gmd:CI_ResponsibleParty>
                            </gmd:citedResponsibleParty>
                        </gmd:CI_Citation>
                    </gmd:thesaurusName>
                </gmd:MD_Keywords>
            </gmd:descriptiveKeywords>
            <gmd:resourceConstraints>
                <gmd:MD_LegalConstraints>
                    <gmd:useLimitation>
                        <gco:CharacterString>respect de la paternitÃ© et de l\'intÃ©griitÃ© des donnÃ©es</gco:CharacterString>
                    </gmd:useLimitation>
                </gmd:MD_LegalConstraints>
            </gmd:resourceConstraints>
            <gmd:resourceConstraints>
                <gmd:MD_SecurityConstraints>
                    <gmd:classification>
                        <gmd:MD_ClassificationCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#unclassified" codeListValue="unclassified"/>
                    </gmd:classification>
                </gmd:MD_SecurityConstraints>
            </gmd:resourceConstraints>
            <gmd:spatialResolution>
                <gmd:MD_Resolution>
                    <gmd:equivalentScale>
                        <gmd:MD_RepresentativeFraction>
                            <gmd:denominator>
                                <gco:Integer>0</gco:Integer>
                            </gmd:denominator>
                        </gmd:MD_RepresentativeFraction>
                    </gmd:equivalentScale>
                </gmd:MD_Resolution>
            </gmd:spatialResolution>
            <gmd:language>
                <gco:CharacterString>fre</gco:CharacterString>
            </gmd:language>
            <gmd:topicCategory>
                <gmd:MD_TopicCategoryCode>biota</gmd:MD_TopicCategoryCode>
            </gmd:topicCategory>
            <gmd:extent>
                <gmd:EX_Extent>
                    <gmd:geographicElement>
                        <gmd:EX_GeographicBoundingBox>
                            <gmd:westBoundLongitude>
                                <gco:Decimal>1.57</gco:Decimal>
                            </gmd:westBoundLongitude>
                            <gmd:eastBoundLongitude>
                                <gco:Decimal>9.21</gco:Decimal>
                            </gmd:eastBoundLongitude>
                            <gmd:southBoundLatitude>
                                <gco:Decimal>41.68</gco:Decimal>
                            </gmd:southBoundLatitude>
                            <gmd:northBoundLatitude>
                                <gco:Decimal>50.48</gco:Decimal>
                            </gmd:northBoundLatitude>
                        </gmd:EX_GeographicBoundingBox>
                    </gmd:geographicElement>
                    </gmd:EX_Extent>
            </gmd:extent>
            <ns9:speciesInformation>
                <ns9:NATSDI_SpeciesInformation>
                    <ns9:taxonomicClassification>
                        <ns9:NATSDI_TaxonomicClassification>
                            <ns9:taxonRankName>
                                <ns9:NATSDI_RankNameCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#species" codeListValue="species"/>
                            </ns9:taxonRankName>
                            <ns9:taxonRankValue>
                                <gmd:MD_Keywords>
                                    <gmd:keyword>
                                        <gco:CharacterString>erithacus rubecula</gco:CharacterString>
                                    </gmd:keyword>
                                    <gmd:type>
                                        <gmd:MD_KeywordTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#taxon" codeListValue="taxon"/>
                                    </gmd:type>
                                </gmd:MD_Keywords>
                            </ns9:taxonRankValue>
                        </ns9:NATSDI_TaxonomicClassification>
                    </ns9:taxonomicClassification>
                    </ns9:NATSDI_SpeciesInformation>
            </ns9:speciesInformation>
        </ns9:NATSDI_DataIdentification>
    </gmd:identificationInfo>
    <gmd:distributionInfo>
        <gmd:MD_Distribution>
            <gmd:distributionFormat>
                <gmd:MD_Format>
                    <gmd:name>
                        <gco:CharacterString>esri shape</gco:CharacterString>
                    </gmd:name>
                    <gmd:version>
                        <gco:CharacterString>inconnu</gco:CharacterString>
                    </gmd:version>
                </gmd:MD_Format>
            </gmd:distributionFormat>
            </gmd:MD_Distribution>
    </gmd:distributionInfo>
    <gmd:dataQualityInfo>
        <gmd:DQ_DataQuality>
            <gmd:scope>
                <gmd:DQ_Scope>
                    <gmd:level>
                        <gmd:MD_ScopeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#dataset" codeListValue="dataset"/>
                    </gmd:level>
                </gmd:DQ_Scope>
            </gmd:scope>
            <gmd:report>
                <gmd:DQ_DomainConsistency>
                    <gmd:result>
                        <gmd:DQ_ConformanceResult>
                            <gmd:specification>
                                <gmd:CI_Citation>
                                    <gmd:title>
                                        <gco:CharacterString>Not yet published</gco:CharacterString>
                                    </gmd:title>
                                    <gmd:date>
                                        <gmd:CI_Date>
                                            <gmd:date>
                                                <gco:DateTime>2010-10-28T00:00:00+02:00</gco:DateTime>
                                            </gmd:date>
                                            <gmd:dateType>
                                                <gmd:CI_DateTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publication" codeListValue="publication"/>
                                            </gmd:dateType>
                                        </gmd:CI_Date>
                                    </gmd:date>
                                </gmd:CI_Citation>
                            </gmd:specification>
                            <gmd:explanation>
                                <gco:CharacterString>See the referenced specification</gco:CharacterString>
                            </gmd:explanation>
                            <gmd:pass>
                                <gco:Boolean>true</gco:Boolean>
                            </gmd:pass>
                        </gmd:DQ_ConformanceResult>
                    </gmd:result>
                </gmd:DQ_DomainConsistency>
            </gmd:report>
            <gmd:lineage>
                <gmd:LI_Lineage>
                    <gmd:statement>
                        <gco:CharacterString>commentaire sur l\'origine d ela donnÃ©e</gco:CharacterString>
                    </gmd:statement>
                    <gmd:processStep>
                        <gmd:LI_ProcessStep>
                            <gmd:description>
                                <gco:CharacterString>brÃ¨ve description du protocole</gco:CharacterString>
                            </gmd:description>
                            <gmd:processor>
                                <gmd:CI_ResponsibleParty>
                                    <gmd:role>
                                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
                                    </gmd:role>
                                </gmd:CI_ResponsibleParty>
                            </gmd:processor>
                        </gmd:LI_ProcessStep>
                    </gmd:processStep>
                    <gmd:source>
                        <gmd:LI_Source>
                            <gmd:description>
                                <gco:CharacterString>??????????????????</gco:CharacterString>
                            </gmd:description>
                            </gmd:LI_Source>
                    </gmd:source>
                </gmd:LI_Lineage>
            </gmd:lineage>
        </gmd:DQ_DataQuality>
    </gmd:dataQualityInfo>
</gmd:MD_Metadata>';
echo $xml_string;
?>

Présentation du

Système d'Information communication:ctt_2010:cenlr

communication:ctt_2010:cenlr:catalogage

Automatisation du renseignement des métadonnées

Utiliser les information contenues dans le SI pour produire la métadonnée

Lots de données

Calcul des autres métadonnées

Fin

communication:ctt_2010:cenlr:collecte

Optimisation du parcours des données au sein du SI du CEN L-R

Création d'une interface web de saisie de données d'observation

Contexte

Besoins initiaux

Besoins étendus et atteins

Travail en concertation avec l'ensemble de l'équipe

Démonstration

Perspectives

communication:ctt_2010:cenlr:diffusion

Mise en ligne d'un lot de données

Cas de l'utilisation de l'ETL Spatial Data Integrator (SDI)

Spatial Data Integrator (SDI)

Mise en ligne d'un lot de données

Mise en ligne d'un lot de données

Processus et composants du job "sicen_carmen"

Perspectives

communication:ctt_2010:cenlr:temp

Requête SQL

Script php

Présentation duSystème d'Information communication:ctt_2010:cenlr

communication:ctt_2010:cenlr:catalogage

communication:ctt_2010:cenlr:collecte

communication:ctt_2010:cenlr:diffusion

communication:ctt_2010:cenlr:temp

Présentation du

Système d'Information communication:ctt_2010:cenlr